题解 | 最长连续登录天数

with a as(
    select 
        *,
        rank() over(partition by user_id order by fdate) as rk
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
),
b as(
    select 
        user_id,
        count(*) as number,
        row_number() over(partition by user_id order by count(*) desc) as rk2
    from a
    group by user_id,fdate-rk
)
select 
    user_id,
    number as max_consec_days
from b
where rk2=1
order by user_id ;