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

WITH T1 AS (SELECT user_id , DATE(log_time) AS login_date FROM login_tb), 

T2 AS (SELECT user_id , login_date, ROW_NUMBER() OVER
(PARTITION BY user_id ORDER BY login_date) AS C1,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER
(PARTITION BY user_id ORDER BY login_date) DAY)
AS C2
FROM T1),

T3 AS (SELECT user_id, COUNT(*) AS consecu_days FROM T2 GROUP BY user_id, C2),

T4 AS (SELECT user_id FROM T3 WHERE consecu_days >= 3)

SELECT T4.user_id FROM T4 INNER JOIN register_tb R ON T4.user_id = R.user_id