题解 | 最长连续登录天数

select 
        user_id,
        max(cnt) max_consec_days
from (
    select 
        user_id,
        sub_fdate,
        count(*) cnt
    from (
        select 
            fdate,
            user_id,
            date_sub(fdate,interval rn day) sub_fdate
        from (
            select
                fdate,
                user_id,
                row_number() over(partition by user_id order by fdate) rn
            from tb_dau
            where fdate between '2023-01-01' and '2023-01-31'
        )a
    )b
    group by user_id,sub_fdate
)c
group by user_id