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

WITH t1 AS (
    -- 第一步：每个用户每天只保留一条记录（按天去重）
    SELECT
        user_id,
        DATE(log_time) AS dt
    FROM login_tb
    JOIN register_tb USING (user_id)
    GROUP BY user_id, dt  -- 关键：同一天只算1次
),
t2 AS (
    -- 第二步：计算连续分组标记 grp
    SELECT
        user_id,
        dt,
        -- 日期 - 行号 = 连续分组（正确写法）
        DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY dt) DAY) AS grp
    FROM t1
),
t3 AS (
    -- 第三步：统计每个连续分组的天数
    SELECT
        user_id,
        grp,
        COUNT(*) AS consec_days
    FROM t2
    GROUP BY user_id, grp
    HAVING consec_days >= 3
)

-- 最终输出满足条件的用户
SELECT DISTINCT user_id
FROM t3
ORDER BY user_id;