题解 | 最长连续登录天数

WITH ranked_logins AS (
    SELECT DISTINCT
        user_id,
        fdate
    FROM tb_dau
    WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
),
grouped_logins AS (
    SELECT
        user_id,
        fdate,
        DATE_SUB(fdate, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) DAY) as grp
    FROM ranked_logins
)
SELECT
    user_id,
    MAX(consecutive_days) as max_consec_days
FROM (
    SELECT
        user_id,
        grp,
        COUNT(*) as consecutive_days
    FROM grouped_logins
    GROUP BY user_id, grp
) t
GROUP BY user_id;