Условие

Из users(user_id, signup_date) и activity(user_id, activity_date) постройте retention-таблицу: для каждой когорты по месяцу регистрации покажите, какая доля пользователей была активна в M+1, M+2, M+3 месяцы после регистрации.

Решение

Подход

Для каждого пользователя получаем когорту = DATE_TRUNC('month', signup_date).
Для каждой активности считаем «номер месяца после регистрации»: DATE_PART('month', age(activity_date, signup_date)) или разница DATE_TRUNC('month', activity) и DATE_TRUNC('month', signup).
Группируем по (cohort, month_offset) и считаем COUNT(DISTINCT user_id).
Делим на размер когорты.

Реализация

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', signup_date)::date AS cohort
    FROM users
),
cohort_size AS (
    SELECT cohort, COUNT(*) AS n_users
    FROM cohorts
    GROUP BY cohort
),
activity_offset AS (
    SELECT
        c.cohort,
        c.user_id,
        (DATE_PART('year',  a.activity_date) - DATE_PART('year',  c.cohort)) * 12
      + (DATE_PART('month', a.activity_date) - DATE_PART('month', c.cohort)) AS month_offset
    FROM cohorts c
    JOIN activity a USING (user_id)
),
retention AS (
    SELECT
        cohort,
        month_offset::int AS m,
        COUNT(DISTINCT user_id) AS retained
    FROM activity_offset
    WHERE month_offset BETWEEN 0 AND 6
    GROUP BY cohort, month_offset
)
SELECT
    r.cohort,
    cs.n_users,
    r.m,
    r.retained,
    ROUND(100.0 * r.retained / cs.n_users, 2) AS retention_pct
FROM retention r
JOIN cohort_size cs USING (cohort)
ORDER BY cohort, m;

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

Доля от размера когорты, а не от M0. Часто пишут r.retained_m / r.retained_m0 — это тоже валидно, но даёт другую метрику («процент тех, кто был активен в M0, дошёл до M_k»).
DATE_PART(month, age(...)) теряет годы. Если когорта 2024-01, а активность 2025-01 — age даст 12 месяцев, а DATE_PART('month', age) — 0. Безопаснее считать разницу через год*12 + месяц.
DISTINCT обязателен: пользователь может быть активен 10 раз в месяце.
Pivot. Финальный вид часто хотят в шахматке месяц-офсетов в колонках. В Postgres — crosstab или вручную MAX(CASE WHEN m=1 THEN retention_pct END).

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

Когорта = месяц регистрации; month_offset = (год_a − год_c) * 12 + (мес_a − мес_c). Сначала COUNT(DISTINCT user_id) по (cohort, offset), потом делите на размер когорты. Не путайте «retention от размера когорты» и «retention от M0».

Aafreen29/SQL-Interview-Prep: когортный retention