题解 | 最长连续登录天数

with data1 as(
    select 
        user_id,
        fdate,
        row_number() over (partition by user_id order by fdate asc) as rn
    from 
        tb_dau
    where YEAR(fdate) = 2023 AND MONTH(fdate) = 1
),data2 as(
    select
        user_id,
        fdate,
        DATE_SUB(fdate,INTERVAL rn day) as day1
    from data1
    
),data3 as (
    select 
        user_id,
        COUNT(1) as max_consec_days
    from data2
    group by 
        user_id,
        day1
)
select
    user_id,
    max(max_consec_days) as max_consec_days
from data3
group by user_id
order by user_id asc