题解 | 最长连续登录天数

with
    temp as (
        SELECT
            user_id,
            fdate,
            row_number() over (
                partition by
                    user_id
                order by
                    fdate
            ) as rn
        from
            tb_dau
    ),
    temp2 as (
        SELECT
            user_id,
            date_sub(fdate, interval rn day) as date_count
        from
            temp t
    ),
    temp3 as (
        select
            user_id,
            count(*) as max_count
        from
            temp2
        group by
            user_id,
            date_count
    )
select
    user_id,
    max(max_count) as max_consec_days
from
    temp3
group by
    user_id;