####第一种方法,将‘login’的过滤出来,组内排序,将结果即为sc####然后将排名为1的找出来并将日期在90天之外的过滤出去,然后分组统计。with sc as(select*,row_number()over(partitionby user_id orderby activity_date)'rankx'from Traffic
where activity='login')select activity_date login_date,count(*) user_count
from sc
where rankx =1and datediff('2019-06-30',activity_date)<=90groupby 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'groupby a.user_id)select c login_date,count(distinct user_id) user_count
from sc
where datediff('2019-06-30',c)<=90groupby c
每日新用户统计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 Traffi.