题解 | 最长连续登录天数

WITH t1 AS (
    SELECT 
        user_id,
        fdate,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) rn
    FROM 
        tb_dau
    WHERE 
        fdate BETWEEN '2023-01-01' AND '2023-01-31'
),
t2 AS (
    SELECT 
        user_id,
        fdate,
        DATE_SUB(fdate, interval t1.rn DAY) grp
    FROM 
        t1
),
t3 AS (
    SELECT 
        user_id,
        count(*) as consecutive_days
    FROM
        t2
    GROUP BY
        user_id,grp
)
SELECT 
    user_id,
    max(consecutive_days) max_consec_days
FROM 
    t3
GROUP BY 
    user_id