Когортный анализ в SQL — практический гайд

Зачем писать когорты в SQL

Когортный анализ — один из главных инструментов продуктовой аналитики. Если вы знаете теорию, но не можете написать SQL-запрос — на собеседовании это заметят. Теоретическую базу мы разбирали в основном гайде по когортам. Здесь — чистая практика: SQL-запросы, которые можно адаптировать под свои данные.

Задача: построить таблицу retention по недельным когортам. Каждая строка — когорта (неделя регистрации), каждый столбец — день (D1, D7, D14, D30), значения — процент вернувшихся пользователей.

Шаг 1: определяем когорту каждого пользователя

Когорта — это группа пользователей с одинаковой датой первого действия. Находим дату первого визита и округляем до недели.

WITH cohort AS (
    SELECT
        user_id,
        MIN(event_date) AS first_date,
        DATE_TRUNC('week', MIN(event_date))::DATE AS cohort_week
    FROM user_activity
    GROUP BY user_id
)

DATE_TRUNC('week', ...) округляет до понедельника. Если нужны месячные когорты — замените на 'month'.

Важно: используйте именно MIN(event_date), а не дату регистрации из таблицы users. Регистрация и первая активность — не всегда одно и то же.

Шаг 2: считаем day_number для каждого визита

Для каждого последующего визита пользователя вычисляем, сколько дней прошло с его первого визита.

activity AS (
    SELECT DISTINCT user_id, event_date
    FROM user_activity
),
retention_data AS (
    SELECT
        c.cohort_week,
        c.user_id,
        a.event_date - c.first_date AS day_number
    FROM cohort c
    INNER JOIN activity a ON c.user_id = a.user_id
)

DISTINCT в activity убирает дубли — если пользователь пришёл дважды за день, это один возврат. day_number = 0 — день первого визита, day_number = 1 — следующий день и т.д.

Шаг 3: агрегируем в когортную таблицу

Для каждой когорты считаем размер и процент вернувшихся на каждый день.

SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 1
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d1,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 7
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d7,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 14
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d14,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 30
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d30
FROM retention_data
WHERE cohort_week <= CURRENT_DATE - INTERVAL '30 days'
GROUP BY cohort_week
ORDER BY cohort_week;

COUNT(DISTINCT CASE WHEN day_number = 7 THEN user_id END) — число уникальных пользователей, которые вернулись ровно на 7-й день. Делим на размер когорты — получаем D7 retention.

Фильтр WHERE cohort_week <= CURRENT_DATE - INTERVAL '30 days' отсекает когорты, у которых D30 ещё не наступил. Без него последние строки покажут 0% и исказят картину.

Полный запрос целиком

Готовый шаблон — скопируйте и подставьте свои таблицы и поля.

WITH cohort AS (
    SELECT
        user_id,
        MIN(event_date) AS first_date,
        DATE_TRUNC('week', MIN(event_date))::DATE AS cohort_week
    FROM user_activity
    GROUP BY user_id
),
activity AS (
    SELECT DISTINCT user_id, event_date
    FROM user_activity
),
retention_data AS (
    SELECT
        c.cohort_week,
        c.user_id,
        a.event_date - c.first_date AS day_number
    FROM cohort c
    INNER JOIN activity a ON c.user_id = a.user_id
)
SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 1
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d1,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 3
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d3,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 7
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d7,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 14
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d14,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number = 30
        THEN user_id END) / COUNT(DISTINCT user_id), 1) AS d30
FROM retention_data
WHERE cohort_week <= CURRENT_DATE - INTERVAL '30 days'
GROUP BY cohort_week
ORDER BY cohort_week;

Если нужны оконные функции для более сложных задач — например, кумулятивный retention или скользящее среднее по когортам, — их можно добавить поверх этого запроса.

Как читать результат

cohort_week	cohort_size	d1	d7	d14	d30
2026-02-03	320	24.1	11.3	7.2	4.1
2026-02-10	285	27.4	13.0	8.8	5.3
2026-02-17	310	31.0	15.8	10.6	6.8
2026-02-24	340	30.3	14.7	9.9	—

Читайте по столбцам: D7 растёт (11.3 → 13.0 → 15.8) — продукт улучшается от когорты к когорте. Последняя строка без D30 — когорта ещё не «дожила» до 30 дней.

Rolling retention vs classic retention

Запрос выше считает classic (day-N) retention — вернулся ли пользователь ровно на N-й день. Есть альтернатива — rolling retention (bounded retention): вернулся ли пользователь на N-й день или позже.

-- Rolling retention: user_id вернулся на день N или позже
ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_number >= 7
    THEN user_id END) / COUNT(DISTINCT user_id), 1) AS rolling_d7

Rolling retention всегда >= classic retention, потому что включает всех, кто пришёл позже. Он лучше подходит для продуктов с нерегулярным использованием (e-commerce, сервисы бронирования), где пользователь может вернуться через 10 дней и это нормально. Classic retention — для продуктов ежедневного использования.

Подробнее про разные виды retention — в гайде по retention и retention в SQL.

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

1. Не отсекать незавершённые когорты. Без фильтра по дате последние когорты покажут 0% на дальних днях. Это создаёт ложное впечатление деградации.

2. Считать retention по сессиям, а не по пользователям. Один активный пользователь с 10 сессиями исказит картину. Используйте COUNT(DISTINCT user_id).

3. Путать день когорты и день активности. day_number — это разница между датой активности и датой первого визита пользователя, а не датой когорты. Ошибка в одну неделю (если когорта — понедельник, а пользователь пришёл в пятницу) сломает все расчёты.

4. Слишком мелкие когорты. Если в когорте 20 человек, один вернувшийся — это 5%. Два — 10%. Случайные колебания будут огромными. Укрупняйте: дни → недели → месяцы.

5. Не учитывать воронку. Когортный анализ показывает retention, но не объясняет почему пользователи уходят. Комбинируйте с воронкой: на каком шаге отваливаются внутри сессии.

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

— Напишите SQL-запрос для когортной таблицы retention. — Три CTE: (1) cohort — MIN(event_date) и DATE_TRUNC для определения когорты, (2) activity — DISTINCT user_id + event_date, (3) retention_data — JOIN с вычислением day_number. Финальный SELECT с COUNT(DISTINCT CASE WHEN day_number = N ...) для каждого дня. Не забыть отфильтровать незавершённые когорты.

— Чем rolling retention отличается от classic retention? — Classic retention (day-N) считает, вернулся ли пользователь ровно на N-й день. Rolling retention — вернулся на N-й день или позже. Rolling >= classic. Classic лучше для ежедневных продуктов, rolling — для продуктов с нерегулярным использованием.

— D7 retention последней когорты резко упал. Как будете разбираться? — Проверить размер когорты. Сегментировать по платформе, источнику трафика, стране. Проверить, не было ли технических проблем (сбои, баги). Сравнить состав когорты с предыдущими — возможно, изменился канал привлечения.

— Как адаптировать запрос для месячных когорт? — Заменить DATE_TRUNC('week', ...) на DATE_TRUNC('month', ...) и вычислять month_number вместо day_number: EXTRACT(YEAR FROM AGE(...)) * 12 + EXTRACT(MONTH FROM AGE(...)). Фильтр незавершённых когорт тоже сдвигается.

FAQ

Какую гранулярность когорт выбрать — дни, недели, месяцы?

Зависит от продукта и трафика. Для мобильных приложений с ежедневным использованием — недели. Для SaaS — месяцы. Для продуктов с маленьким трафиком (< 100 регистраций в неделю) укрупняйте до месяцев, иначе когорты будут слишком маленькими для выводов.

Можно ли строить когорты не по дате регистрации?

Да. Когорты можно строить по любому первому событию: первая покупка, первое прохождение онбординга, первое использование фичи. Также можно строить поведенческие когорты — по действию, а не по дате. Например, «прошли онбординг» vs «не прошли». Подробнее — в основном гайде.

Как визуализировать когортную таблицу?

Лучший формат — heatmap (тепловая карта). В Python — seaborn.heatmap(), в Google Sheets — условное форматирование, в BI-инструментах — встроенные шаблоны. Обязательно подписывайте размер когорты рядом с процентами, чтобы не обманываться маленькими выборками.

---

Потренируйте вопросы по SQL — откройте тренажёр. 1500+ вопросов для собеседования аналитика. Бесплатно.