题解 | 最长连续登录天数

select
    user_id,
    max(count) as max_consec_days
from
(
select
    user_id,
    count(*) as count
from
    (
        select
            user_id,
            date_sub(fdate, interval xuhao day) as ds
        from
            (
            select
            user_id,
            fdate,
            row_number() over (partition by user_id order by fdate) as xuhao
            from tb_dau
            where
            year(fdate) = 2023
            and month(fdate) = 1
            group by user_id,fdate
            order by user_id
            ) a
    ) b
group by user_id,ds
) c
group by user_id
;