SQL: Построить когортную таблицу retention по месяцам

Решение

###概念理解

Когортная таблица retention показывает, какой процент пользователей, зарегистрировавшихся в одном месяце (когорта), остались активны в последующие месяцы.

• M0 — месяц регистрации
• M1 — месяц через месяц после регистрации
• M2 — два месяца спустя
• И т.д.

SQL-запрос

WITH cohorts AS (
  -- Определяем когорту (месяц регистрации) для каждого пользователя
  SELECT
    user_id,
    DATE_TRUNC("month", registration_date)::date as cohort_month,
    EXTRACT(YEAR FROM registration_date)::integer as cohort_year
  FROM users
  WHERE EXTRACT(YEAR FROM registration_date) = 2024
),
active_users AS (
  -- Определяем месяцы активности каждого пользователя
  SELECT DISTINCT
    user_id,
    DATE_TRUNC("month", event_date)::date as activity_month
  FROM events
  WHERE event_type != "test_event" -- исключаем служебные события
),
user_months AS (
  -- Соединяем когорты с активностью
  SELECT
    c.cohort_month,
    c.user_id,
    a.activity_month,
    (DATE_PART("year", a.activity_month) - DATE_PART("year", c.cohort_month)) * 12 +
    (DATE_PART("month", a.activity_month) - DATE_PART("month", c.cohort_month))::integer as months_since_registration
  FROM cohorts c
  LEFT JOIN active_users a ON c.user_id = a.user_id
  WHERE a.activity_month >= c.cohort_month -- активность не раньше регистрации
),
cohort_sizes AS (
  -- Размер каждой когорты
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) as cohort_size
  FROM cohorts
  GROUP BY cohort_month
),
retention_data AS (
  -- Считаем активных пользователей на каждом месяце
  SELECT
    um.cohort_month,
    um.months_since_registration,
    COUNT(DISTINCT um.user_id) as active_users
  FROM user_months um
  WHERE um.months_since_registration >= 0
  GROUP BY um.cohort_month, um.months_since_registration
)
SELECT
  rd.cohort_month,
  cs.cohort_size,
  MAX(CASE WHEN rd.months_since_registration = 0 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M0,
  MAX(CASE WHEN rd.months_since_registration = 1 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M1,
  MAX(CASE WHEN rd.months_since_registration = 2 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M2,
  MAX(CASE WHEN rd.months_since_registration = 3 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M3,
  MAX(CASE WHEN rd.months_since_registration = 4 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M4,
  MAX(CASE WHEN rd.months_since_registration = 5 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M5,
  MAX(CASE WHEN rd.months_since_registration = 6 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M6,
  MAX(CASE WHEN rd.months_since_registration = 7 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M7,
  MAX(CASE WHEN rd.months_since_registration = 8 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M8,
  MAX(CASE WHEN rd.months_since_registration = 9 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M9,
  MAX(CASE WHEN rd.months_since_registration = 10 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M10,
  MAX(CASE WHEN rd.months_since_registration = 11 THEN ROUND(100.0 * rd.active_users / cs.cohort_size, 2) ELSE NULL END) as M11
FROM retention_data rd
JOIN cohort_sizes cs ON rd.cohort_month = cs.cohort_month
GROUP BY rd.cohort_month, cs.cohort_size
ORDER BY rd.cohort_month;

Пошаговое объяснение

1. cohorts — выделяем пользователей 2024 года и их месяц регистрации
2. active_users — определяем месяцы активности (DISTINCT нужна, так как пользователь может совершить несколько событий в месяц)
3. user_months — объединяем и считаем, сколько месяцев прошло с регистрации
4. cohort_sizes — размер каждой когорты (всего пользователей, зарегистрировавшихся в месяц)
5. retention_data — агрегируем активных пользователей по месяцам
6. Финальный SELECT — преобразуем в формат таблицы с M0, M1, M2...

Вывод

| cohort_month | cohort_size | M0 | M1 | M2 | M3 | ... | |---|---|---|---|---|---|-|-| | 2024-01-01 | 1500 | 100.00 | 85.33 | 72.67 | 65.20 | ... | | 2024-02-01 | 1800 | 100.00 | 88.89 | 75.56 | ... | ... | | 2024-03-01 | 2100 | 100.00 | 86.67 | ... | ... | ... |

Ключевые моменты

• M0 всегда 100% (если пользователь зарегистрировался)
• DATE_TRUNC приводит дату к началу месяца
• LEFT JOIN нужен для отслеживания пользователей, которые не вернулись
• EXTRACT считает месяцы между датами
• ROUND округляет проценты до 2 знаков
• Можно использовать PIVOT (SQL Server) для более компактного представления