| cohort | 首日集合 |
|---|---|
| 分母 | 首日新增数 |
| 分子 | 第 N 日活跃数 |
次日/7日留存
次日/7日留存
WITH cohort AS (
SELECT user_id, min(dt) AS first_dt
FROM dwd_active_user GROUP BY user_id
)
SELECT c.first_dt,
count(DISTINCT CASE WHEN a.dt = date_add(c.first_dt, 1) THEN a.user_id END) / count(DISTINCT c.user_id) AS d1,
count(DISTINCT CASE WHEN a.dt = date_add(c.first_dt, 7) THEN a.user_id END) / count(DISTINCT c.user_id) AS d7
FROM cohort c
LEFT JOIN dwd_active_user a ON a.user_id = c.user_id
GROUP BY c.first_dt;分母含「首日之后被召回」的用户会让留存虚高，cohort 一旦生成应冻结。