题解 | 最长连续登录天数

WITH
t1 AS (     -- 清洗数据，得出每个人都在哪些天登陆过
SELECT
    DISTINCT fdate,user_id
FROM tb_dau
WHERE fdate >= '2023-01-01' AND fdate < '2023-02-01'
),

t2 AS (     -- 用窗口函数对登录日期进行排序，目的是为了接下来可以用排序结果进行每次连续登录的分组
SELECT
    fdate,user_id,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS rk
FROM t1
),

t3 AS (     -- 对每次连续登录进行分组
SELECT
    user_id,
    fdate,
    DATE_SUB(fdate,INTERVAL (rk - 1) DAY) AS fz
-- 同一次连续登录在这一步的计算结果是相同的，每次连续登录中断时，计算结果都会发生变化，比如第一次连续登录是排序1 1日登录，排序2 2日登录，第二次连续登录是排序3 4日登录，排序4 5日登录，则第一组两天的计算结果都为：1-0=1 2-1=1，第二组为：4-2=2 5-3=2，这样就可以用这个计算结果来进行分组，以开启后续的计算
FROM t2
),

t4 AS (     -- 计算每组连续登录的天数
SELECT
    user_id,
    COUNT(*) AS days
FROM t3
GROUP BY user_id,fz
)

SELECT      -- 主查询计算每位用户的最大连续登录天数
    user_id,
    MAX(days) AS max_consec_days
FROM t4
GROUP BY user_id;