题解 | 最长连续登录天数

SELECT T3.user_id,
MAX(T3.continue_days) AS max_consec_days
FROM 
(
    SELECT T2.user_id, T2.n_date, 
    COUNT(*) AS continue_days
    FROM
    (
        SELECT *,
        DATE_SUB(T1.fdate, INTERVAL rn DAY) AS n_date
        FROM 
        (
            SELECT *, 
            ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS rn
            FROM 
            (SELECT DISTINCT fdate, user_id FROM  tb_dau) AS T0 # 去除同一用户一天多次登录的影响
            WHERE T0.fdate BETWEEN '2023-01-01' AND '2023-01-31' 
        ) AS T1 # 对每个用户按登录日期排序
    ) AS T2 # 用登陆日期减去排名，若连续登录，则得到的日期相等
    GROUP BY T2.user_id, T2.n_date
) AS T3 # 计算每个用户的连续登录天数
GROUP BY T3.user_id; # 选出每个用户最大的连续登陆天数