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

WITH t AS(
    SELECT fdate, user_id,
           DENSE_RANK() over (PARTITION BY user_id ORDER BY fdate) AS rk
    FROM tb_dau
    WHERE YEAR(fdate) = 2023 AND MONTH(fdate) = 1           
),
t1 AS(
    SELECT t.*,
          DATE_SUB(fdate, INTERVAL rk DAY) AS diff_day
    FROM t
),
t2 AS (
    SELECT user_id, COUNT(diff_day) AS consec_days
    FROM t1
    GROUP BY user_id, diff_day
)
    SELECT user_id, MAX(consec_days) AS max_consec_days
    FROM t2
    GROUP BY user_id;