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

WITH T1 AS (
    SELECT
        user_id,
        date(log_time) fdate,
        row_number() over(partition by user_id order by date(log_time)) rn
    FROM login_tb JOIN register_tb USING(user_id)
),
T2 AS (
    SELECT 
        user_id,
        DATE_SUB(fdate, interval rn day) grp
    FROM T1
),
T3 AS (
    SELECT 
        user_id,
        grp,
        count(*) consecutive_days
    FROM T2
    GROUP BY user_id,grp
)
SELECT 
    user_id
FROM T3
WHERE consecutive_days >= 3