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

WITH t AS (
    SELECT
        lt.user_id,
        lt.log_time,
        -- 为每个用户的登录时间分配一个序号
        row_number() over (PARTITION BY lt.user_id ORDER BY lt.log_time) rn
    FROM login_tb AS lt
    JOIN register_tb AS rt ON lt.user_id = rt.user_id #确保用户是新注册用户
)
SELECT DISTINCT t1.user_id
FROM t AS t1
JOIN t AS t2 ON t1.user_id = t2.user_id AND t2.rn = t1.rn + 1 AND DATEDIFF(t2.log_time, t1.log_time) = 1 
JOIN t AS t3 ON t1.user_id = t3.user_id AND t3.rn = t1.rn + 2 AND DATEDIFF(t3.log_time, t2.log_time) = 1
WHERE DATEDIFF(t3.log_time, t1.log_time) = 2;