题解 | 最长连续登录天数

select 
    t2.user_id,
    max(consec_days) as max_consec_days
from
    (select
        user_id,
        count(*) as consec_days
    from (select 
            user_id,
            fdate,
            row_number()over(partition by user_id order by fdate) as "序号",
            date_sub(fdate,interval row_number()over(partition by user_id order by fdate) day) as "分组日期"
        from tb_dau
        where fdate between "2023-01-01" and "2023-01-31"
        group by user_id,fdate) as t 
    group by user_id,分组日期) as t2
group by t2.user_id