题解 | 最长连续登录天数

with login_sequence as (
    select distinct
        user_id,
        fdate
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
),
user_groups as (
    select
        user_id,
        fdate,
        sum(case 
        when exists(
            select 1
            from login_sequence t2
            where t2.user_id = t1.user_id
            and t2.fdate = date_sub(t1.fdate,interval 1 day)
        ) then 0
        else 1
        end) over(partition by user_id order by fdate) as group_id
    from login_sequence t1  
),
consecutive_days as (
    select 
        user_id,
        group_id,
        count(*) as consec_days
    from user_groups 
    group by user_id,group_id
)

select
    user_id,
    max(consec_days) as max_consec_days
from consecutive_days
group by user_id