题解 | 最长连续登录天数

with
    t1 as (
        select
            fdate,
            user_id,
            row_number() over (
                partition by
                    user_id
                order by
                    fdate
            ) rk
        from
            tb_dau
    ),
    t2 as (
        select
            user_id,
            fdate,
            date_sub(fdate, interval rk day) as grp
        from
            t1
    )
select
    user_id,
    max(log_days) max_consec_days
from
    (
        select
            user_id,
            count(grp) log_days
        from
            t2
        group by
            user_id,
            grp
    ) temp
group by
    user_id

t1表利用窗口函数row_number给每个用户（partition by 分区）的登录记录按日期排序分配唯一且连续的行号；

t2表计算每个用户登录日期与对应行号差值的日期，如果用户的登录是连续的，那么同一组连续登录日期计算得到的 grp 值是相同的；