题解 | 最长连续登录天数

WITH N1 AS(
    SELECT *, 
    DENSE_RANK() OVER(PARTITION BY user_id ORDER BY fdate ASC) AS rnk
    FROM tb_dau
),
N2 AS(
    SELECT user_id, COUNT(*) AS cnt 
    FROM N1
    GROUP BY user_id, DATE_SUB(fdate, INTERVAL rnk DAY)
)
SELECT
    user_id,
    MAX(cnt) AS max_consec_days
FROM N2
GROUP BY user_id