Постройте когортную таблицу retention (неделя регистрации × неделя активности) с помощью SQL на PostgreSQL?

Короткий ответ

Нужно определить для каждого пользователя неделю когорты (неделя первой активности), затем для каждой пары (неделя когорты, номер недели с момента входа) посчитать число уникальных активных пользователей и разделить на размер когорты.

Как это работает подробнее

сначала строим таблицу когорт (user_id → cohort_week), затем джойним с событиями, вычисляем week_number = date_part('week', event) - date_part('week', cohort) (или через разницу дат, поделённую на 7), группируем по (cohort_week, week_number) и делим на размер когорты.

Пример кода

cohort_retention.sqlКопировать

-- PostgreSQL
WITH cohorts AS (
  SELECT user_id, date_trunc('week', MIN(event_date))::date AS cohort_week
  FROM events
  GROUP BY user_id
),
activity AS (
  SELECT
    c.cohort_week,
    c.user_id,
    (date_trunc('week', e.event_date)::date - c.cohort_week) / 7 AS week_number
  FROM cohorts c
  JOIN events e ON e.user_id = c.user_id
)
SELECT
  cohort_week,
  week_number,
  COUNT(DISTINCT user_id) AS active_users,
  COUNT(DISTINCT user_id)::numeric /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_week ORDER BY week_number) AS retention
FROM activity
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

Что использовать на практике

• Практика. использование date_trunc('week', ...) зависит от того, с какого дня недели считать (в PostgreSQL неделя по умолчанию начинается с понедельника, ISO-8601); для продуктов без выраженной недельной сезонности можно использовать дни вместо недель, но для B2C-продуктов с выраженной разницей будни/выходные недельные когорты обычно устойчивее к шуму.

Подводные камни

• Частые ошибки. считают week_number простым вычитанием номеров календарных недель без учёта перехода через границу года (неделя 52 минус неделя 1 даёт отрицательное число); не исключают дубликаты событий на пользователя в рамках одной недели, что не искажает retention (т.к. используется DISTINCT), но замедляет запрос на больших объёмах.