题解 | 最长连续登录天数

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