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

SELECT DISTINCT user_id
FROM (
    SELECT 
        user_id,
        log_date,
        LAG(log_date, 2) OVER (PARTITION BY user_id ORDER BY log_date) AS prev2_date
    FROM (
        SELECT DISTINCT user_id, DATE(log_time) AS log_date
        FROM login_tb
    ) AS distinct_logins
) AS A
WHERE user_id IN (SELECT user_id FROM register_tb)
  AND DATEDIFF(log_date, prev2_date) = 2
ORDER BY user_id;