题解 | 最长连续登录天数

WITH date_num 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'
),
sd AS (
    SELECT
        user_id,
        fdate,
        DATE_SUB(fdate, INTERVAL rn DAY) AS start_date
    FROM date_num
),
consec_groups as
(
    select user_id ,start_date,count(*) as consec_days
    from sd
    group by user_id,start_date
)

SELECT
    user_id,
    MAX(consec_days) AS max_consec_days
FROM consec_groups
GROUP BY user_id;