Условие
Таблица events(user_id, event_dt, event_type). Постройте когортную таблицу удержания:
- строки — неделя первого события пользователя (
week_0); - колонки —
week_offset(0, 1, 2, ... до 12); - значение — доля пользователей когорты, вернувшихся в неделю N (
% retained).
Решение
Подход
- На каждого пользователя — неделя первого события (
cohort_week). - Для каждой активности —
week_offset = (event_week - cohort_week) / 7. - Группируем по
cohort_week, week_offset, считаем уникальных пользователей. - Делим на размер когорты, чтобы получить долю.
Запрос
WITH user_first AS (
SELECT user_id,
date_trunc('week', MIN(event_dt))::date AS cohort_week
FROM events
GROUP BY user_id
),
activity AS (
SELECT
uf.cohort_week,
date_trunc('week', e.event_dt)::date AS event_week,
e.user_id
FROM events e
JOIN user_first uf ON uf.user_id = e.user_id
WHERE e.event_dt >= uf.cohort_week
),
agg AS (
SELECT
cohort_week,
((event_week - cohort_week) / 7)::int AS week_offset,
COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_week, week_offset
),
sizes AS (
SELECT cohort_week, active_users AS cohort_size
FROM agg WHERE week_offset = 0
)
SELECT
a.cohort_week,
s.cohort_size,
a.week_offset,
a.active_users,
ROUND(a.active_users * 100.0 / s.cohort_size, 1) AS pct_retained
FROM agg a
JOIN sizes s USING (cohort_week)
WHERE a.week_offset BETWEEN 0 AND 12
ORDER BY a.cohort_week, a.week_offset;Pivot для отчётa
WITH r AS (... /* запрос выше */)
SELECT
cohort_week, cohort_size,
MAX(CASE WHEN week_offset = 0 THEN pct_retained END) AS w0,
MAX(CASE WHEN week_offset = 1 THEN pct_retained END) AS w1,
MAX(CASE WHEN week_offset = 2 THEN pct_retained END) AS w2,
MAX(CASE WHEN week_offset = 3 THEN pct_retained END) AS w3,
MAX(CASE WHEN week_offset = 4 THEN pct_retained END) AS w4,
MAX(CASE WHEN week_offset = 8 THEN pct_retained END) AS w8,
MAX(CASE WHEN week_offset = 12 THEN pct_retained END) AS w12
FROM r
GROUP BY cohort_week, cohort_size
ORDER BY cohort_week;Альтернатива — generate_series для пустых ячеек
Если хотим, чтобы недели без активности показывались как 0:
WITH user_first AS (...),
weeks AS (
SELECT generate_series(0, 12) AS week_offset
),
expected AS (
SELECT uf.cohort_week, w.week_offset
FROM user_first uf
CROSS JOIN weeks w
GROUP BY 1, 2
)
SELECT e.*, COALESCE(a.active_users, 0) AS active
FROM expected e
LEFT JOIN agg a USING (cohort_week, week_offset);Подводные камни
MIN(event_dt)через все типы событий: иногда «когорту» нужно строить только поsignup, а активность — по любым. ТогдаMIN(event_dt) WHERE event_type='signup'.- Часовые пояса:
date_trunc('week', event_dt)— стандарт ISO (понедельник). В BQ/Snowflake — параметрWEEK(start_day). - Свежие когорты «не созрели»: для когорты с
cohort_week = 2025-05-19week_offset = 12ещё не наступил → ячейка должна быть NULL, а не 0. COUNT(DISTINCT)дорого на 100М строк — заранее свернуть доuser_id × week.- Граница недели: если событие в воскресенье 23:59 vs понедельник 00:01 — попадут в разные cohort_week. Решение —
date_trunc('week', ...)+ явно следить за TZ. - «Cohort by month»: тот же скрипт, заменить
'week'на'month'и7на 1 (offset тогда в месяцах через(year, month)арифметику). - Pivot с динамическими колонками — обычно невозможен в чистом SQL без procedural; pivot делают в BI-инструменте.
Эталонный ответ
WITH user_first AS (
SELECT user_id, date_trunc('week', MIN(event_dt))::date AS cohort_week
FROM events GROUP BY user_id
),
agg AS (
SELECT uf.cohort_week,
((date_trunc('week', e.event_dt)::date - uf.cohort_week) / 7)::int AS week_offset,
COUNT(DISTINCT e.user_id) AS active_users
FROM events e JOIN user_first uf USING (user_id)
GROUP BY 1, 2
)
SELECT cohort_week, week_offset, active_users,
active_users * 100.0 / FIRST_VALUE(active_users)
OVER (PARTITION BY cohort_week ORDER BY week_offset) AS pct_retained
FROM agg
WHERE week_offset BETWEEN 0 AND 12
ORDER BY cohort_week, week_offset;Ключевые элементы: MIN(event_dt) для cohort_week; offset через арифметику дат; деление на cohort_size через FIRST_VALUE или JOIN; не путать свежие «незрелые» когорты с нулевыми.