题解 | #最长连续登录天数#

select
    user_id,
    max(days) as max_consec_days
from
    (select
        user_id,
        start_date,
        max(date_rank) - min(date_rank) + 1 as 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 start_date
        from 
            tb_dau
        where 
            fdate between '2023-01-01' and '2023-01-31') t1
    group by 
        1,2) t2
group by 
    1