题解 | 最长连续登录天数

-- 逻辑拆解：这种题目的核心解法分为两步，1：识别连续登录天数，2：求出用户最长的登录天数
-- 对于1，我们可以利用窗口函数，增加一列按日期排序的rank列；对于2，我们可以对日期减去rank列的结果进行分组，计数，求出最大值。（核心逻辑是：如果用户是连续登录，那么其日期-rank列的结果一定相同）
With ranked AS(
    SELECT
        a.user_id,
        a.min_date,
        DENSE_RANK() OVER(partition by a.user_id order by a.min_date) ranking
    FROM(
        SELECT
            user_id,
            DATE(fdate),
            MIN(fdate) min_date
        FROM tb_dau
        WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
        GROUP BY user_id,DATE(fdate) -- 这一步的目的是为了消除一个用户某一天多次登录
    ) a
),
grouped AS(
    SELECT
        t1.user_id,
        t1.diff,
        COUNT(t1.diff) cnt_consec_days
    FROM(
        SELECT
            user_id,
            DATE_SUB(min_date,INTERVAL ranking DAY) diff -- 这个地方可以有效避免跨越的情况，虽然本题不存在
        FROM ranked
    ) t1
    GROUP BY t1.user_id,t1.diff
)
SELECT 
    user_id,
    MAX(cnt_consec_days) max_consec_days
FROM grouped
GROUP BY user_id