mysql 力扣，1107. 每日新用户统计

1107. 每日新用户统计
      https://leetcode-cn.com/problems/new-users-daily-count/
      
      

####第一种方法，将‘login’的过滤出来，组内排序，将结果即为sc
####然后将排名为1的找出来并将日期在90天之外的过滤出去，然后分组统计。
with sc as 
    (select *,row_number() over(partition by user_id order by  activity_date)'rankx'
    from Traffic
    where activity='login')
select activity_date login_date,count(*) user_count
from sc 
where rankx = 1 and datediff('2019-06-30',activity_date)<=90 
group by activity_date

###类似于，组内排序，直接两表按照userid和activity = 'login'合并，并按照单表的a.user_id聚合，取日期的最小值，记做sc
###从sc中将超过90天的过滤，然后按照日期聚合，统计。
with sc as 
    (select a.user_id,min(a.activity_date) c
    from traffic a join traffic b on a.user_id=b.user_id and a.activity = 'login'and b.activity = 'login'
    group by a.user_id)
select c login_date,count(distinct user_id) user_count
from sc
where  datediff('2019-06-30',c)<=90 
group by c