题解 | 最长连续登录天数

select 
user_id,
consec_days as max_consec_days
from (
    select 
    user_id,
    consec_days,
    row_number() over (partition by user_id order by consec_days desc) as days_rank
    from (
        select ranked_tb.user_id,
        count(*) as consec_days
        from (
            select 
            Date(fdate) as dt,
            user_id,
            dense_rank() over (partition by user_id order by  fdate asc) as sorted_id
            from  tb_dau
            where fdate between '2023-01-01' and '2023-01-31' ) as ranked_tb
        group by date_sub(ranked_tb.dt, interval sorted_id day),user_id ) as grouped_tb) as grouped_ranked_tb
where days_rank=1