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

WITH t1 AS (SELECT l.user_id,
                   DATE_FORMAT(log_time, '%Y-%m-%d')                          AS log_date,
                   ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_time) AS rn
            FROM login_tb l
                     JOIN register_tb r ON l.user_id = r.user_id AND l.log_time >= r.reg_time),
     t2 AS (SELECT user_id, log_date, rn, DATE_SUB(log_date, INTERVAL rn DAY) AS rn2 FROM t1),
     t3 AS (SELECT user_id, rn2, COUNT(1) AS login_days FROM t2 GROUP BY user_id, rn2)
SELECT user_id
FROM t3
WHERE login_days >= 3;