Retention в SQL — как написать запрос

Коротко

Retention — доля пользователей, которые вернулись в продукт через определённое время после первого визита. D1 retention 40% значит: из 100 новых пользователей 40 пришли снова на следующий день. Метрика показывает, нашёл ли продукт ценность для аудитории. На собеседованиях аналитиков задачи на retention в SQL — один из самых популярных типов. Если хотите разобраться в метрике без SQL — начните со статьи как считать retention.

Что именно измеряет retention

Retention отвечает на один вопрос: кто вернулся? Не сколько раз пользователь зашёл, не сколько действий совершил — а был ли он активен через N дней после первого визита.

Классические точки замера:

• D1 — вернулся на следующий день. Показывает первое впечатление.
• D7 — вернулся через неделю. Показывает, сформировалась ли привычка.
• D30 — вернулся через месяц. Показывает долгосрочную ценность.

Retention считают по когортам: берут группу пользователей, пришедших в один день (или неделю), и смотрят, какая доля из них вернулась на день 1, 7, 30.

Пошаговый SQL-запрос

Допустим, есть таблица activity с колонками user_id и event_date (дата активности, без времени). Строим retention по дневным когортам.

Шаг 1. Первая активность каждого пользователя

SELECT user_id, MIN(event_date) AS cohort_date
FROM activity
GROUP BY user_id

cohort_date — это дата первого визита. Она определяет, к какой когорте относится пользователь.

Шаг 2. Присоединяем последующую активность

WITH cohorts AS (
    SELECT user_id, MIN(event_date) AS cohort_date
    FROM activity
    GROUP BY user_id
)
SELECT
    c.user_id,
    c.cohort_date,
    a.event_date,
    a.event_date - c.cohort_date AS day_offset
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id

day_offset — сколько дней прошло от первого визита до данной активности. D0 — сам день регистрации, D1 — следующий день и так далее.

Шаг 3. Группируем по когорте и дню

WITH cohorts AS (
    SELECT user_id, MIN(event_date) AS cohort_date
    FROM activity
    GROUP BY user_id
),
with_offset AS (
    SELECT
        c.cohort_date,
        c.user_id,
        a.event_date - c.cohort_date AS day_offset
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
)
SELECT
    cohort_date,
    day_offset,
    COUNT(DISTINCT user_id) AS active_users
FROM with_offset
GROUP BY cohort_date, day_offset
ORDER BY cohort_date, day_offset

Шаг 4. Считаем процент

WITH cohorts AS (
    SELECT user_id, MIN(event_date) AS cohort_date
    FROM activity
    GROUP BY user_id
),
cohort_size AS (
    SELECT cohort_date, COUNT(*) AS total_users
    FROM cohorts
    GROUP BY cohort_date
),
with_offset AS (
    SELECT
        c.cohort_date,
        c.user_id,
        a.event_date - c.cohort_date AS day_offset
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
)
SELECT
    w.cohort_date,
    w.day_offset,
    COUNT(DISTINCT w.user_id) AS active_users,
    s.total_users,
    ROUND(100.0 * COUNT(DISTINCT w.user_id) / s.total_users, 1) AS retention_pct
FROM with_offset w
JOIN cohort_size s ON s.cohort_date = w.cohort_date
WHERE w.day_offset IN (1, 7, 14, 30)
GROUP BY w.cohort_date, w.day_offset, s.total_users
ORDER BY w.cohort_date, w.day_offset

Фильтр day_offset IN (1, 7, 14, 30) оставляет только нужные точки. Без него получите retention для каждого дня — полезно для кривой, но избыточно для отчёта.

Rolling retention vs classic retention

Классический (day-N) retention отвечает на вопрос: «был ли пользователь активен именно на день N?». Rolling retention — «был ли пользователь активен на день N или позже?».

Rolling retention всегда >= classic, потому что учитывает всех, кто вернулся хотя бы раз после дня N. Его используют, когда продукт не предполагает ежедневного использования (например, маркетплейс или сервис бронирования).

-- Rolling retention: пользователь активен на день N или позже
WITH cohorts AS (
    SELECT user_id, MIN(event_date) AS cohort_date
    FROM activity
    GROUP BY user_id
),
cohort_size AS (
    SELECT cohort_date, COUNT(*) AS total_users
    FROM cohorts
    GROUP BY cohort_date
)
SELECT
    c.cohort_date,
    s.total_users,
    COUNT(DISTINCT CASE WHEN a.event_date >= c.cohort_date + 7 THEN c.user_id END) AS rolling_d7,
    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN a.event_date >= c.cohort_date + 7 THEN c.user_id END)
        / s.total_users, 1
    ) AS rolling_d7_pct
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
JOIN cohort_size s ON s.cohort_date = c.cohort_date
GROUP BY c.cohort_date, s.total_users
ORDER BY c.cohort_date

На собеседовании могут попросить объяснить разницу или написать оба варианта. Подробнее о связи retention и оттока — в статье retention vs churn.

Кривая retention

Кривая retention — это график, где по оси X — день (D0, D1, D7, D30), по оси Y — процент вернувшихся. Типичная форма: резкое падение в первые дни (D0→D1 часто теряет 50-70% пользователей), затем кривая выравнивается и выходит на плато.

Плато — хороший знак: значит, оставшиеся пользователи нашли ценность и продолжают пользоваться продуктом. Если кривая не выравнивается и стремится к нулю — у продукта нет product-market fit. Для подготовки SQL-запроса кривой нужны все day_offset, а не только контрольные точки — уберите фильтр WHERE day_offset IN (...) из запроса выше.

Типичные ошибки

Считают события, а не пользователей. COUNT(event_date) вместо COUNT(DISTINCT user_id). Если пользователь зашёл 5 раз на D7, он всё равно один вернувшийся, а не пять.

Неправильная арифметика дат. В PostgreSQL разность дат даёт integer (дни). Но если event_date — это timestamp, а не date, разность будет interval. Приведите к date: a.event_date::date - c.cohort_date::date.

Включают день 0 в retention. D0 — это день первого визита. Retention D0 всегда 100% по определению. Его не нужно показывать как метрику и тем более считать достижением.

Не обрезают незрелые когорты. Если когорта существует 5 дней, D7 retention для неё ещё не определён. Показывать его как 0% — ошибка. Фильтруйте: WHERE cohort_date <= CURRENT_DATE - day_offset.

Путают classic и rolling. Classic D7 — активен именно на 7-й день. Rolling D7 — активен на 7-й день или позже. Без уточнения подразумевается classic.

Вопросы с собеседований

— Напишите SQL-запрос для D1, D7, D30 retention по когортам. — CTE с MIN(event_date) для когорт, JOIN с таблицей активности, вычисляем day_offset как разность дат, группируем по когорте и day_offset, делим COUNT(DISTINCT user_id) на размер когорты.

— Чем rolling retention отличается от классического? — Классический считает, был ли пользователь активен именно на день N. Rolling — был ли активен в день N или позже. Rolling >= classic всегда. Rolling используют для продуктов с нерегулярным использованием.

— Как посчитать retention только для пользователей из определённого канала? — Добавляем JOIN с таблицей users и фильтруем по каналу привлечения. Когортный размер тоже считаем с этим фильтром, иначе процент будет некорректным.

— Retention D7 = 0%. Это баг в данных или реальная проблема? — Сначала проверить: достаточно ли прошло времени (когорта должна существовать >= 7 дней). Затем — есть ли данные активности вообще. Если данные корректны и когорта зрелая — это реальная проблема продукта.

— Как улучшить retention с точки зрения аналитики? — Сегментировать: по каналу, устройству, поведению в первой сессии. Найти сегменты с высоким retention и понять, чем они отличаются. Построить воронку первой сессии — возможно, пользователи не доходят до ценности продукта.

---

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

FAQ

Чем retention отличается от churn?

Retention — доля вернувшихся, churn — доля ушедших. В простейшем случае churn = 100% - retention. Но на практике определения churn могут отличаться: когда считать пользователя ушедшим, учитывать ли реактивацию. Подробнее — в статье retention vs churn.

Какой retention считается хорошим?

Зависит от продукта. Для мобильных приложений D1 ~25-30% — уже неплохо, D30 ~5-10% — норма. Для SaaS месячный retention 95%+ — ожидание. Сравнивать нужно с бенчмарками своей категории, а не с абстрактным «хорошим» числом.

Как связаны retention и когортный анализ?

Retention — это метрика, когортный анализ — метод. Retention считают по когортам, чтобы видеть динамику: улучшается ли удержание с новыми версиями продукта. Без когорт retention — одно усреднённое число, которое скрывает тренд. Подробнее — в гайде по когортному анализу.

Как тренироваться

Retention-запросы на собеседованиях требуют уверенного владения CTE, JOIN и условной агрегацией. В SQL-тренажёре Карьерник есть задачи на когортный анализ, оконные функции и расчёт метрик — с пошаговыми разборами. Больше вопросов — в разделе с примерами.