题解 | 最长连续登录天数

with a1 as (
    select fdate as base_date ,user_id
    from tb_dau
    where date(fdate) between '2023-01-01' and '2023-01-31'
    group by fdate ,user_id
),

a2 as (
    select base_date ,user_id,rank() over(partition by user_id order by base_date) as rk
    from a1
),

a3 as (
    select *,base_date - rk as u
    from a2
),

a4 as(
    select user_id,u,count(*) as con_date
    from a3
    group by user_id,u
)

select user_id,max(con_date) as max_consec_days
from a4
group by user_id