题解 | 最长连续登录天数

select user_id, max(consec_days) as max_consec_days from (
    select 
        user_id, 
        count(ranking) as consec_days
        from (
        select
            user_id,
            day(fdate) - (row_number() over(partition by user_id order by fdate)) as ranking
        from tb_dau
        where left(fdate,7) = '2023-01') t1
    group by user_id, ranking
    order by user_id, consec_days desc)t2
group by user_id