题解 | 最长连续登录天数

select user_id,max(num) as max_consec_days
from (
    select user_id, ori_date, count(distinct fdate) as num
        from (
            select user_id, fdate, date_sub(fdate, interval rn day) as ori_date
            from (
                select user_id, fdate, dense_rank() over(partition by user_id order by fdate) as rn
                from tb_dau
                where fdate between '2023-01-01' and '2023-01-31'
            ) a
        ) b
        group by user_id, ori_date
) c
group by user_id;