题解 | 最长连续登录天数

WITH t1 AS(
    SELECT
        user_id,
        DATE(fdate) AS fdate
    FROM
        tb_dau
),
t2 AS(
    SELECT
        user_id,
        fdate,
        ROW_NUMBER() OVER(
            PARTITION BY user_id
            ORDER BY fdate ASC
        ) AS rk
    FROM
        t1
),
t3 AS(
    SELECT
        user_id,
        DATE_SUB(fdate, INTERVAL rk DAY) AS consec_group,
        COUNT(1) AS cnt
    FROM
        t2
    GROUP BY
        user_id,
        consec_group
)
SELECT
    user_id,
    MAX(cnt) AS max_consec_days
FROM
    t3
GROUP BY
    user_id
ORDER BY
    user_id ASC;

SQL经典题