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

WITH t1 AS (
    SELECT fdate,
           user_id
    FROM tb_dau
    WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
    GROUP BY fdate, user_id
),
t2 AS (
    SELECT fdate,
           user_id,
           row_number()
               over (PARTITION BY user_id
                   ORDER BY fdate ASC) AS ranking
    FROM t1
),
t3 AS (
    SELECT
        user_id,
        DATE_ADD(fdate, INTERVAL - ranking DAY ) AS after_date
    FROM t2
),
t4 AS (
    SELECT
        user_id,
        after_date,
        count(after_date) AS max_consec_days
    FROM t3
    GROUP BY user_id, after_date
),
t5 AS (
    SELECT
        user_id,
        max_consec_days,
        RANK() over (PARTITION BY user_id ORDER BY max_consec_days DESC) AS ranking
    FROM t4
),
t6 AS (
    SELECT user_id,
           max_consec_days
    FROM t5
    WHERE ranking = 1
    GROUP BY user_id, max_consec_days
    ORDER BY user_id ASC 
)
SELECT * FROM t6;

# 这道题是求用户最高连续登录天数，这里是以用户作为事件驱动，因此有多少个用户，就有多少行结果！