sql问题总结(持续更新)

sql问题(持续更新)

用户连续日活表usr_login，表中字段：userid，dt，目前该表存有1-30号内的数据，请输出30天内，连续登录三天及以上的userid，以及用户初始活跃的第一天

CREATE TABLE usr_login (
    userid INT,
    dt DATE
);

WITH user_logins AS (
    SELECT
        userid,
        dt,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY dt) AS row_num
    FROM usr_login
),
login_streaks AS (
    SELECT
        userid,
        dt,
        row_num,
        DATE_SUB(dt, INTERVAL row_num DAY) AS streak_start
    FROM user_logins
),
grouped_streaks AS (
    SELECT
        userid,
        MIN(dt) AS first_active_day,
        COUNT(*) AS streak_length
    FROM login_streaks
    GROUP BY userid, streak_start
)
SELECT
    userid,
    first_active_day
FROM grouped_streaks
WHERE streak_length >= 3
ORDER BY userid, first_active_day;