题解 | 最长连续登录天数

SELECT
    user_id,
    MAX(consec_days) AS max_consec_days
 FROM 
    (
        SELECT 
            b.user_id,
            b.first_date,
            COUNT(*) AS consec_days
        FROM 
            (
                SELECT 
                    a.user_id,
                    date_sub(fdate,interval a.rn day) as first_date
                FROM
                    (
                        SELECT 
                            user_id, 
                            fdate,
                            ROW_NUMBER()over(partition by user_id order by fdate) as rn
                        FROM tb_dau
                    ) a
            )b
        GROUP BY b.user_id,b.first_date
    ) c
GROUP BY user_id

学了评论区大佬的代码

1. 连续日期 → fdate - row_number() = 固定值 → 按这个值分组 → 统计每段长度
2. date_sub(fdate,interval a.rn day)，先设置一个连续的参照物