题解 | 最长连续登录天数

select
    user_id ,
    max(rankdifcnt) AS max_consec_days #由于rank1和rank2都是连续排序的，当连续登录时，每日的序号差相等，故取序号差计数最多的为最大连续登录数
from(
    select
        t.user_id,
        t.user_rank2 - t.user_rank1 as rankdif , #日期序号-实际登录序号=序号差辅助列
        count(t.user_rank2 - t.user_rank1) as rankdifcnt #对辅助列的值进行计数
    from(
        select 
            user_id,
            rank() over (partition by user_id order by fdate asc) as user_rank1 ,#记录实际登录序号
            DAY(fdate) as user_rank2 #记录日期序号
        from tb_dau td
        where td.fdate between '2023-01-01' and '2023-01-31'
    )t
    group by t.user_id,(t.user_rank2 - t.user_rank1)
)tt
group by user_id