题解 | 最长连续登录天数

select t3.user_id, max(t3.countnum) as max_consec_days
from(
    select t2.user_id, count(t2.datesub) as countnum
        from(
            select t1.fdate,
                t1.user_id,
                row_number()over(partition by t1.user_id order by t1.fdate) as rk,
                date_sub(t1.fdate, interval row_number()over(partition by t1.user_id order by t1.fdate) day) as datesub
            from tb_dau t1
            where fdate between '2023-01-01' and '2023-01-31'
        ) as t2
    group by t2.user_id,t2.datesub
) as t3
group by t3.user_id