题解 | 查询连续登陆的用户

SELECT user_id
FROM (
    SELECT 
        user_id,
        COUNT(*) AS consecutive_days
    FROM (
        SELECT 
            user_id,
            DATE(log_time) AS login_date,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(log_time)) AS rn
        FROM login_tb
        WHERE user_id IN (SELECT user_id FROM register_tb)
        GROUP BY user_id, DATE(log_time)  -- 替代DISTINCT去重
    ) AS numbered
    GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY)
) AS grouped
WHERE consecutive_days >= 3
ORDER BY user_id;

还可以这么写