题解 | 最长连续登录天数

select 
    t3.user_id
    ,max(t3.days_cnt) as max_consec_days
from (
    select 
        t2.user_id
        ,t2.fday-t2.rn as daysdiff
        ,count(1) as days_cnt
    from (
        select 
            t1.*
            ,day(t1.fdate) as fday
            ,row_number() over(partition by t1.user_id order by t1.fdate) as rn
        from (
            select
            distinct 
            t.user_id
            ,t.fdate
            from tb_dau t
            where t.fdate between date'2023-01-01' and date'2023--1-31'
        ) t1
    ) t2
    group by 1,2
) t3
group by 1