Посчитайте retention первой недели по когортам недели регистрации
Проверила команда Сопровод · Обновлено
Короткий ответ
Определите когорту как неделю регистрации: date_trunc('week', registered_at). Присоедините к пользователям сессии через LEFT JOIN с условием, что визит пришелся на 7–13 день после регистрации. Вернувшихся считайте через COUNT(DISTINCT s.user_id), размер когорты — через COUNT(DISTINCT c.id). Делите на всю когорту, включая тех, кто ни разу не зашел.
Почему этот вопрос задают
От senior ждут, что он сначала договорится об определении метрики и только потом напишет запрос. Ошибка в знаменателе или в границах окна дает красивый retention, на котором команда потом принимает неверные решения.
Условие задачи
Приложение для медитаций ведет таблицы users(id, registered_at) и sessions(user_id, visited_at), даты без времени, все в 2026 году. Менеджер продукта хочет сравнить когорты недели регистрации: какая доля пользователей вернулась с 7 по 13 день после регистрации, день регистрации нулевой. В сессиях бывают повторы в один день. Верните неделю когорты, ее размер, число вернувшихся и долю в процентах, отсортируйте по неделе.
Как подойти к решению
- Договоритесь об определении: день регистрации нулевой, окно с 7 по 13 день
- Посчитайте неделю когорты через
date_trunc('week', registered_at) - Присоедините сессии через
LEFT JOINи поставьте условие на окно вON - Считайте пользователей через
COUNT(DISTINCT): сессий у одного человека бывает несколько
Решение
Условие на окно стоит в ON, поэтому пользователи без визитов остаются в знаменателе. Если перенести его в WHERE, в обеих когортах останутся только вернувшиеся, и retention станет 100. COUNT(s.user_id) без DISTINCT посчитает повторные сессии, и у когорты от 07.09 доля выйдет 66.7 при верных 33.3. Обе таблицы читаются один раз, COUNT(DISTINCT) добавляет сортировку внутри групп.
WITH cohorts AS (
SELECT id, registered_at, date_trunc('week', registered_at)::date AS cohort_week
FROM users
)
SELECT c.cohort_week,
COUNT(DISTINCT c.id) AS cohort_size,
COUNT(DISTINCT s.user_id) AS returned,
ROUND(100.0 * COUNT(DISTINCT s.user_id) / COUNT(DISTINCT c.id), 1)
AS retention_pct
FROM cohorts c
LEFT JOIN sessions s
ON s.user_id = c.id
AND s.visited_at BETWEEN c.registered_at + 7 AND c.registered_at + 13
GROUP BY c.cohort_week
ORDER BY c.cohort_week;cohort_week | cohort_size | returned | retention_pct -------------+-------------+----------+--------------- 2026-09-07 | 6 | 2 | 33.3 2026-09-14 | 4 | 2 | 50.0 (2 rows)
Что спросят следом
Как не занизить retention у последней когорты?
Что изменится, если
registered_atхранится какtimestamptz?Как построить кривую retention по дням с 1 по 30 одним запросом?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«В когорту возьму тех, у кого есть хоть одна сессия»
Знаменатель — все зарегистрированные за неделю, даже без единого визита. Иначе когорта 07.09 сожмется до пяти человек, и retention вырастет с 33.3 до 40.0
«Посчитаю
COUNT(*)по сессиям за дни 7–13»Сессии повторяются: пользователь 4 зашел дважды 17.09. Считайте людей через
COUNT(DISTINCT user_id), иначе доля когорты 07.09 выйдет 66.7 вместо 33.3«Условие на дату визита поставлю в
WHERE»WHEREвыбросит пользователей без визитов в окне, и в знаменателе останутся только вернувшиеся: retention станет 100. Перенесите условие вONуLEFT JOIN«Первая неделя — это визиты с 1 по 7 день»
Согласуйте границы с интервьюером. Если день регистрации нулевой, неделя 0 — дни с 0 по 6, неделя 1 — с 7 по 13. Сдвиг на день ломает сравнение с прошлыми отчетами
Сигналы для интервьюера
Сильный ответ
Сам называет пограничные случаи из данных: визит ровно на 13 день, визит на 14 день и две сессии в один день
Тревожный сигнал
Решает задачу, но не может пересчитать одну когорту вручную, когда интервьюер просит проверить ответ
Красный флаг
Сравнивает когорты по числу вернувшихся без учета размера и делает вывод, что удержание не изменилось
Как еще могут спросить
- Как посчитать retention недели 1 по когортам?
- Напишите запрос когортного анализа: доля пользователей, вернувшихся через неделю после регистрации
- Как посчитать долю вернувшихся пользователей по неделям регистрации?
- Как в PostgreSQL разбить пользователей на когорты по неделе регистрации и посчитать возврат?











