题解 | #最长连续登录天数#

with
    tmp as (
        select
            user_id,
            fdate,
            lag (fdate, 1, "0000-00-00") over (
                partition by
                    user_id
                order by
                    fdate
            ) fadte_sub
        from
            tb_dau
        where
            fdate between "2023-01-01" and "2023-01-31"
    ) #使用lag 窗口函数，按用户id分组，登陆日期升序，进行日期偏移,
    tmp2 as (
        select
            user_id,
            fdate,
            fadte_sub,
            datediff (fdate, fadte_sub) as diff_date,
            if (datediff (fdate, fadte_sub) < 2, "0", fdate) as dt_line
        from
            tmp
    ) # 登陆日期和偏移日期作差，如果日期差小于2（连续日期差为1，间隔一天算连续日期差为2，其余以此类推），则为连续登录，记作0，否则为当前登录日期。,
    tmp3 as (
        select
            *,
            max(dt_line) over (
                partition by
                    user_id
                order by
                    fdate
            ) as max_dt_line
        from
            tmp2
    ) # 利用窗口函数max，将连续登录的日期修正成起始登录日期
select
    user_id,
    max(consec_days) as max_consec_days
from
    (
        select
            user_id,
            max_dt_line,
            count(*) as consec_days
        from
            tmp3
        group by
            user_id,
            max_dt_line
    ) tmp4
group by
    user_id # 根据起始登录日期分组找到最大值