题解 | 最长连续登录天数

-- 在现有的tb_dau基础上通过窗口函数新增一列
-- 再加入一个辅助列 Column2 = f_date - Column1 用DATE_SUB函数

WITH T1 AS (SELECT fdate, user_id, 
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS Column1,
DATE_SUB(fdate, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) DAY) AS Column2
FROM tb_dau),

T2 AS (SELECT user_id, COUNT(*) AS consec_days
FROM T1 
GROUP BY user_id, Column2)

SELECT user_id, MAX(consec_days)  AS max_consec_days
FROM T2
GROUP BY user_id