题解 | 最长连续登录天数

WITH grouped_date as(
SELECT fdate,user_id,
date_sub(fdate, interval ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY fdate) day) date_rank
FROM tb_dau
WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
)
SELECT user_id, MAX(consec_days) max_consec_days FROM(
SELECT user_id, date_rank, COUNT(*) consec_days FROM grouped_date
GROUP BY user_id, date_rank
) sub_query
GROUP BY user_id