题解 | 最长连续登录天数

select 
    user_id,
    max(consecutive_days) max_consec_days
from
    (select
        user_id,
        max(date_rank) - min(date_rank) + 1 consecutive_days
    from
        (select
            user_id,
            fdate,
            row_number() over(partition by user_id order by fdate) as date_rank,
            date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as date_start
        from
            tb_dau
        where
            fdate between '2023-01-01' and '2023-01-31') a
    group by
        user_id,
        date_start
    ) b
group by
    user_id