InterviewQuery — Subscription Retention: 3-месячная когортная сетка

Условие

Таблица subscriptions(user_id, plan_id, start_date, end_date). end_date может быть NULL (активная подписка).

Постройте когортную таблицу retention за первые 3 месяца для каждой пары (cohort_month, plan_id):

• cohort_month — DATE_TRUNC('month', start_date),
• cohort_size — сколько пользователей в когорте,
• retention_m1, m2, m3 — доля активных в 1-й, 2-й, 3-й месяц после start_date.

«Активен в месяце M» = end_date либо NULL, либо >= start_of_month + M.

Решение

WITH base AS (
  SELECT
    DATE_TRUNC('month', start_date)::DATE AS cohort_month,
    plan_id,
    user_id,
    start_date,
    COALESCE(end_date, DATE '9999-12-31') AS effective_end
  FROM subscriptions
),
sized AS (
  SELECT
    cohort_month, plan_id,
    COUNT(*) AS cohort_size,
    SUM(CASE WHEN effective_end >= start_date + INTERVAL '1 month' THEN 1 ELSE 0 END) AS retained_m1,
    SUM(CASE WHEN effective_end >= start_date + INTERVAL '2 months' THEN 1 ELSE 0 END) AS retained_m2,
    SUM(CASE WHEN effective_end >= start_date + INTERVAL '3 months' THEN 1 ELSE 0 END) AS retained_m3
  FROM base
  GROUP BY cohort_month, plan_id
)
SELECT
  cohort_month, plan_id, cohort_size,
  ROUND(retained_m1::DECIMAL / cohort_size, 4) AS retention_m1,
  ROUND(retained_m2::DECIMAL / cohort_size, 4) AS retention_m2,
  ROUND(retained_m3::DECIMAL / cohort_size, 4) AS retention_m3
FROM sized
ORDER BY cohort_month, plan_id;

Семантика «retained_m1»

«Пользователь дожил до 1 месяца» — end_date >= start_date + 1 month (либо ещё активен). Если у юзера end_date < start + 1 month — он отвалился в первый месяц.

Альтернатива: «retained_m1 = был активен на конец первого календарного месяца» — другая формула, и часто продукты считают её именно так. Уточняйте на собесе.

Деталь — COALESCE(end_date, '9999-12-31')

Активные подписки имеют NULL. Заменяем «бесконечностью», чтобы все сравнения >= ... срабатывали без отдельных IS NULL веток.

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

1. Незрелые когорты. Когорта мая 2025 не может иметь retention_m3, если сейчас июль 2025. Фильтр: cohort_month <= CURRENT_DATE - INTERVAL '3 months'.
2. Семантика «в каком месяце». «retained_m1» = «прошёл 1 месяц» vs «активен в M+1 календарного». Разница на тестах ±1 неделя.
3. COUNT(*) vs COUNT(DISTINCT user_id). Если один user может иметь несколько подписок на один план в одной когорте — нужен DISTINCT.
4. start_date + INTERVAL '1 month'. В Postgres корректно прибавляет 1 календарный месяц. В MySQL — DATE_ADD(start_date, INTERVAL 1 MONTH).

Эталонный ответ

DATE_TRUNC('month', start) → COUNT(*) + SUM(CASE end >= start + N month), отношение к cohort_size. Фильтр незрелых когорт обязателен в проде.