InterviewQuery — Annual Retention: годовая ретенция в discounted plan

Условие

Таблица subscriptions(user_id, start_date, end_date) (NULL = активна). Посчитайте среднюю ретенцию по году: для когорт начала 2020, 2021, ..., 2024 какая доля дожила до конца следующего года.

Выход: cohort_year, cohort_size, retained_to_next_year, retention_pct.

Решение

WITH base AS (
  SELECT
    EXTRACT(YEAR FROM start_date)::INT AS cohort_year,
    user_id,
    start_date,
    COALESCE(end_date, DATE '9999-12-31') AS eff_end
  FROM subscriptions
),
agg AS (
  SELECT
    cohort_year,
    COUNT(*) AS cohort_size,
    SUM(
      CASE WHEN eff_end >= (DATE (cohort_year + 1 || '-12-31'))
           THEN 1 ELSE 0 END
    ) AS retained_to_next_year
  FROM base
  GROUP BY cohort_year
)
SELECT
  cohort_year,
  cohort_size,
  retained_to_next_year,
  ROUND(100.0 * retained_to_next_year / cohort_size, 2) AS retention_pct
FROM agg
ORDER BY cohort_year;

Простое усреднение

Если просят «средняя ретенция по годам» (одно число для отчёта):

SELECT ROUND(AVG(retention_pct)::NUMERIC, 2) FROM ...;

Внимание: усреднение долей — это не «общая доля». Если когорты разные по размеру, нужно weighted average:

SELECT ROUND(100.0 * SUM(retained_to_next_year)::DECIMAL / SUM(cohort_size), 2)
FROM agg;

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

1. Незрелая когорта. Когорту 2025 нельзя оценить «до конца 2026», если 2026 ещё не наступил. Фильтр: cohort_year < EXTRACT(YEAR FROM CURRENT_DATE) - 1.
2. Average of averages. Простое AVG(retention_pct) ≠ суммарная доля. Объясните разницу.
3. DATE (cohort_year + 1 || '-12-31'). Конкатенация числа со строкой — Postgres дешевле через MAKE_DATE(cohort_year + 1, 12, 31).

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

EXTRACT(YEAR FROM start_date) → группировка → SUM(CASE eff_end >= конец след. года) → отношение. Среднее по годам — weighted, иначе будет искажение.