题解 | 最长连续登录天数

#断点分组
select
user_id
,max(cnt_consec_days) as max_consec_days
from
    (select
    user_id
    ,fdate
    ,count(consec_days)over(partition by user_id,consec_days) as cnt_consec_days
    from
        (select
        user_id
        ,fdate
        ,pd
        ,sum(pd)over(partition by user_id order by fdate) as consec_days
        from
            (select
            user_id
            ,fdate
            ,lag_fdate
            ,case 
                when datediff(fdate,lag_fdate)=1 then 0
                when lag_fdate is null then 1
                else 1 end  as pd
            from
                (select
                user_id
                ,fdate
                ,lag(fdate,1)over(partition by user_id order by fdate) as lag_fdate
                from tb_dau)t1
                )t2)t3)t4
group by 1;