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

WITH t1 AS (
    SELECT user_id,
           DATE(log_time) AS log_date
    FROM login_tb
    WHERE user_id in
    (SELECT user_id FROM register_tb)
    GROUP BY user_id, DATE(log_time)
),
t2 AS (
    SELECT user_id,
           log_date,
           RANK() over (PARTITION BY user_id
               ORDER BY log_date) AS ranking
    FROM t1
),
t3 AS (
    SELECT user_id,
           date_add(log_date,INTERVAL -ranking DAY) AS after_date
    FROM t2
),
t4 AS (
    SELECT user_id,
           after_date,
           COUNT(after_date) AS cont
    FROM t3
    GROUP BY user_id, after_date
)
SELECT user_id
       FROM t4
WHERE cont >= 3
ORDER BY user_id ASC;

# 常规的连续登录问题!