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

WITH
t1 AS (
SELECT
    user_id,
    fdate,
    RANK()OVER(PARTITION BY user_id ORDER BY fdate) AS rk
FROM tb_dau
WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
)

SELECT
    user_id,
    MAX(cnt) AS max_consec_days
FROM
(
SELECT
    user_id,
    DATE_SUB(fdate, INTERVAL rk DAY) AS sdate,
    COUNT(1) AS cnt
FROM t1
GROUP BY user_id, sdate
) AS m
GROUP BY user_id;