编辑代码

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;