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'GROUPBY
dt
),
next_day_retention AS (
SELECT
a.dt,
COUNT(DISTINCTCASEWHEN b.user_id ISNOTNULLTHEN a.user_id END) AS retained_users
FROM
active_user_di a
LEFTJOIN
active_user_di b ON a.user_id = b.user_id ANDDATE_ADD(a.dt, INTERVAL1DAY) = b.dt
WHERE
a.dt BETWEEN'2022-11-01'AND'2022-11-29'GROUPBY
a.dt
)
SELECT
dau.dt,
dau.active_users,
COALESCE(retained_users, 0) AS retained_users
FROM
daily_active_users dau
LEFTJOIN
next_day_retention ndr ON dau.dt = ndr.dt
ORDERBY
dau.dt;