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

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