Когортный retention: Day 7 и Day 30
// Условие
Для каждой когорты (месяц регистрации, формат YYYY-MM) посчитайте:
— количество пользователей в когорте (cohort_size)
— долю тех, кто совершил хотя бы одну завершённую покупку через 7 и более дней после регистрации (retention_d7)
— долю тех, кто совершил завершённую покупку через 30 и более дней (retention_d30)
Округлите доли до 2 знаков (от 0.00 до 1.00). Выведите cohort_month, cohort_size, retention_d7, retention_d30. Отсортируйте по cohort_month.
// Таблицы
user_id | INT |
registration_date | DATE |
acquisition_channel | VARCHAR(20) |
platform | VARCHAR(10) |
city | VARCHAR(50) |
is_premium | BOOLEAN |
order_id | INT |
user_id | INT |
order_date | TIMESTAMP |
amount | NUMERIC(10,2) |
status | VARCHAR(20) |
delivery_days | INT |
promo_code | VARCHAR(20) |
// Что тренирует
// Решение
Подсказка
LEFT JOIN users → orders. Когорта = TO_CHAR(DATE_TRUNC('month', registration_date), 'YYYY-MM'). Retention = COUNT(DISTINCT CASE WHEN order_date >= registration_date + INTERVAL '7 days' THEN user_id END) / COUNT(DISTINCT user_id).
Сначала попробуй сам → Показать решение
SELECT
TO_CHAR(DATE_TRUNC('month', u.registration_date), 'YYYY-MM') AS cohort_month,
COUNT(DISTINCT u.user_id) AS cohort_size,
ROUND(
COUNT(DISTINCT CASE
WHEN o.order_date >= u.registration_date + INTERVAL '7 days'
AND o.status = 'completed'
THEN u.user_id
END)::numeric / COUNT(DISTINCT u.user_id),
2
) AS retention_d7,
ROUND(
COUNT(DISTINCT CASE
WHEN o.order_date >= u.registration_date + INTERVAL '30 days'
AND o.status = 'completed'
THEN u.user_id
END)::numeric / COUNT(DISTINCT u.user_id),
2
) AS retention_d30
FROM sandbox.users u
LEFT JOIN sandbox.orders o ON u.user_id = o.user_id
GROUP BY 1
ORDER BY 1;Задача уровня финального собеседования. Ключевые моменты:
(1) LEFT JOIN — чтобы не потерять пользователей без заказов (они входят в знаменатель).
(2) COUNT(DISTINCT CASE WHEN ... THEN user_id END) — условная агрегация. Считаем уникальных пользователей, у которых есть заказ через 7+ дней.
(3) ::numeric перед делением — без этого PostgreSQL сделает целочисленное деление и вернёт 0.
(4) Последние когорты будут иметь retention_d30 ≈ 0, потому что не прошло 30 дней — это нормально.
Связь со статьёй «Retention: как считать» на сайте.