1. 取2022年11月每日活跃用户数及次日留存

WITH daily_active_users AS (
    SELECT
        dt,
        COUNT(DISTINCT user_id) AS active_users
    FROM
        active_user_di
    WHERE
        dt BETWEEN '2022-11-01' AND '2022-11-30'
    GROUP BY
        dt
),
next_day_retention AS (
    SELECT
        a.dt,
        COUNT(DISTINCT CASE WHEN b.user_id IS NOT NULL THEN a.user_id END) AS retained_users
    FROM
        active_user_di a
    LEFT JOIN
        active_user_di b ON a.user_id = b.user_id AND DATE_ADD(a.dt, INTERVAL 1 DAY) = b.dt
    WHERE
        a.dt BETWEEN '2022-11-01' AND '2022-11-29'
    GROUP BY
        a.dt
)
SELECT
    dau.dt,
    dau.active_users,
    COALESCE(retained_users, 0) AS retained_users
FROM
    daily_active_users dau
LEFT JOIN
    next_day_retention ndr ON dau.dt = ndr.dt
ORDER BY
    dau.dt;

1. 取出每天时长最长的十个用户

WITH ranked_users AS (
    SELECT
        user_id,
        duration,
        RANK() OVER (PARTITION BY dt ORDER BY duration DESC) AS rank
    FROM
        active_user_di
    WHERE
        dt BETWEEN '2022-11-01' AND '2022-11-30'
)
SELECT
    user_id,
    duration
FROM
    ranked_users
WHERE
    rank <= 10;