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

WITH tb1 AS (
    SELECT user_id, date_format(log_time, '%Y-%m-%d') AS log_date,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_time ASC) AS rk
    FROM login_tb
    WHERE user_id IN (SELECT user_id FROM register_tb)
),
tb2 AS (
SELECT user_id, DATE_SUB(log_date, INTERVAL rk DAY) AS grp
FROM tb1
),
tb3 AS (
SELECT user_id, COUNT(*) AS consec_days
FROM tb2
GROUP BY user_id,grp
)
SELECT user_id
FROM tb3
WHERE consec_days >= 3