题解 | 最长连续登录天数

select user_id, max(consec_days) as max_consec_days
from (
    select user_id, count(*) as consec_days
    from (
        select user_id,
               date_sub(fdate, interval diff day) as consec
        from (
            select user_id,
                   fdate,
                   row_number() over (partition by user_id order by fdate) as diff
            from tb_dau
        ) as c1
    ) as c2
    group by user_id, consec
) as c3
group by user_id;

1.从原始表中取值user_id, fdate, row_number() over (partition by user_id order by fdate) as diff。diff指以user_id划分为区后，每一个区中的登陆日期排名。构建完成c1表

2.从c1表中取值user_id, date_sub(fdate, interval diff day) as consec。consec指原表的fdate-fdate的排名所得到的差值。因为排名一定是连续的，所以若日期为连续，则差值相等；若不连续，则差值不等。构建完成c2表

3.从c2表中取值，首先先以user_id, consec对数据进行分组，含义是每一个用户中，日期差值相等的数据。若差值相同，则日期连续。求和，count(*) as consec_days即可得到每一段连续登录的时间范围中连续登录的天数。构建完成c3表

4.从c3表中取值，首先因为要求不同用户的最大登录天数，故group by user_id进行用户分组，随后在每个分组中求连续登录日期最大值max(consec_days) as max_consec_days，完成解答。