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

WITH login_diff AS(
    SELECT rt.user_id,
            DATEDIFF(DATE(lt.log_time), DATE(rt.reg_time)) AS date_diff,
            ROW_NUMBER() OVER (PARTITION BY rt.user_id ORDER BY DATE(lt.log_time)) AS row_num
    FROM register_tb AS rt
    LEFT JOIN login_tb AS lt
    ON lt.user_id = rt.user_id
),
date_diff AS(
    SELECT user_id,
        (CAST(date_diff AS SIGNED) - CAST(row_num AS SIGNED)) AS diff
    FROM login_diff
),
seq_counts AS(
    SELECT *,
        COUNT(*) AS seq_days
    FROM date_diff
    GROUP BY user_id, diff
)
SELECT user_id 
FROM seq_counts
WHERE seq_days >= 3
ORDER BY user_id;