题解 | #最长连续登录天数# 白痴也能看懂

with相当于mybatis中的sql标签，可以重复利用查询得到的表

#t1.对每个用户的登录日期做排名
with t1 as(
   select user_id,fdate,row_number() over(
      partition by user_id
      order by fdate
   ) as row_num from tb_dau

),t2 as(
    #t2.对于连续登录的日期d1,d2,d3,...dk,排名为r1,r2,r3,...,rk。则date_sub(di,interval ri day),1<=i<=k,的值是恒等的（可自行手动验证）,基于t1，求出每个用户的date_sub(di,interval ri day)值，记作diff
    select user_id,date_sub(fdate,interval row_num day) as diff,fdate,row_num from t1
),t3 as(
    #3.同一个用户，某个diff值重复的个数，就是对应一段连续登录的日期长度，基于t2，分组求出每个用户每个diff值的个数
    select user_id,count(diff) as consec_d from t2 group by user_id,diff
 )
#4.基于t3，取每个用户的最大diff值重复次数，作为最长登录时间
 select user_id,max(consec_d) as max_consec_days from t3 group by user_id