Постройте недельную когортную таблицу retention в SQL из таблиц signups и activity
Есть signups(user_id, signup_week) и activity(user_id, active_week) — по строке на пользователя за каждую неделю его активности. Постройте когортную таблицу retention: для каждой когорты signup_week посчитайте, сколько пользователей были активны при weeks_since_signup = 0, 1, 2, … после регистрации, где "удержан в неделю k" значит, что пользователь был активен в неделю через k недель после регистрации.
Ограничения: строки это когорта регистрации, мера это недели с момента регистрации, и счётчик недели 0 каждой когорты это знаменатель её retention. Не считайте пользователя дважды в ячейке.
-- signup_week, weeks_since_signup, retained_users
-- (retention = retained_users / размер когорты в неделю 0)Напишите запрос.
Соедините activity с signups по user_id, выведите weeks_since_signup = active_week - signup_week, затем GROUP BY signup_week, weeks_since_signup с COUNT(DISTINCT user_id). Счётчик недели 0 каждой когорты это знаменатель для её retention.
- ✗Группировать по календарной неделе вместо недель-с-регистрации
- ✗Брать знаменателем все регистрации вместо недели 0 каждой когорты
- ✗Использовать COUNT(*) вместо COUNT(DISTINCT user_id) и считать дважды
- →Как переключить это с недельных когорт на N-day?
- →Как заполнить пропущенные недели, чтобы дыры были 0, а не отсутствующими строками?
Соедините activity с signups, посчитайте смещение недели и сгруппируйте по когорте и неделе-с-регистрации, беря COUNT(DISTINCT user_id):
WITH cohort AS (
SELECT s.user_id,
s.signup_week,
a.active_week - s.signup_week AS weeks_since_signup
FROM signups s
JOIN activity a ON a.user_id = s.user_id
WHERE a.active_week >= s.signup_week
)
SELECT signup_week,
weeks_since_signup,
COUNT(DISTINCT user_id) AS retained_users,
ROUND(
COUNT(DISTINCT user_id) * 1.0
/ MAX(COUNT(DISTINCT user_id)) OVER (PARTITION BY signup_week),
3
) AS retention_rate
FROM cohort
GROUP BY signup_week, weeks_since_signup
ORDER BY signup_week, weeks_since_signup;weeks_since_signup = 0 даёт размер когорты; оконный MAX(...) OVER (PARTITION BY signup_week) берёт его как знаменатель, поэтому неделя 0 всегда равна 1.0. COUNT(DISTINCT user_id) не даёт задвоить пользователя, а фильтр active_week >= signup_week отбрасывает активность до регистрации.
