Czytaj książkę: «Когорты в SQL. 18 задач по удержанию»
Перед первой задачей
Зачем нужны когорты
Общий средний показатель часто смешивает старых и новых клиентов так, что становится непонятно, что реально улучшается.
Когорта фиксирует момент входа клиента и позволяет смотреть активацию, удержание и деньги во времени.
Эта книга тренирует SQL-запросы для когорт без внешних BI-инструментов и без личных данных.
Главный контроль
Всегда показывайте размер когорты рядом с процентом, иначе маленькая группа может выглядеть как большой успех.
Различайте календарный месяц, точное число дней и возраст когорты: это разные правила.
Все примеры запускаются в SQLite на маленьких встроенных таблицах.
Маршрут по задачам
1. Разнести клиентов по месяцу регистрации
2. Посчитать размер когорты
3. Найти первую покупку в когорте
4. Посчитать активацию когорты
5. Посчитать удержание на следующий месяц
6. Посчитать retention rate
7. Развернуть когорту по возрасту месяца
8. Построить матрицу revenue по двум месяцам
9. Сравнить когорты по прибыли
10. Найти когорты без повторной покупки
11. Проверить когорту по каналу
12. Посчитать активность на седьмой день
13. Найти клиентов, вернувшихся после паузы
14. Сравнить когорты по среднему чеку
15. Посчитать заказы на клиента в когорте
16. Найти когорты с маленьким размером
17. Собрать компактную таблицу когорт
18. Посчитать выручку первых 30 дней
Практические задачи
Задача 1. Разнести клиентов по месяцу регистрации
Рабочий вопрос
Нужно получить когорту каждого клиента по месяцу регистрации. Это нужно для отчёта, который можно проверить без ручной правки.
Данные
CREATE TABLE customers(client_id INTEGER, registered_at TEXT);
INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');
Запрос
SELECT client_id, substr(registered_at,1,7) AS cohort_month
FROM customers
ORDER BY client_id;
Ожидаемый результат
[[10, "2026-08"], [20, "2026-08"], [30, "2026-09"]]
Почему работает
substr берёт YYYY-MM из даты формата YYYY-MM-DD и создаёт простую месячную когорту.
Запрос сделан на маленькой синтетической таблице, поэтому ответ можно проверить глазами и повторить в SQLite.
Явный ORDER BY или отдельная агрегация фиксируют смысл результата, а не оставляют его на случайный порядок строк.
Где легко ошибиться
Если даты хранятся не в ISO-формате, такой приём может дать неверную группировку.
Самостоятельная проверка
Какая когорта у клиента 30? Объясните ответ по исходным строкам и условию запроса.
Ответ: 2026-09. Это следует из исходных строк и условия запроса.
Задача 2. Посчитать размер когорты
Рабочий вопрос
Нужно узнать, сколько клиентов вошло в каждую когорту регистрации. Это нужно для отчёта, который можно проверить без ручной правки.
Данные
CREATE TABLE customers(client_id INTEGER, registered_at TEXT);
INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');
Запрос
SELECT substr(registered_at,1,7) AS cohort_month, COUNT(*) AS cohort_size
FROM customers
GROUP BY cohort_month
ORDER BY cohort_month;
Ожидаемый результат
[["2026-08", 2], ["2026-09", 1]]
Почему работает
GROUP BY по месяцу регистрации собирает клиентов в когорты и считает размер каждой.
Запрос сделан на маленькой синтетической таблице, поэтому ответ можно проверить глазами и повторить в SQLite.
Явный ORDER BY или отдельная агрегация фиксируют смысл результата, а не оставляют его на случайный порядок строк.
Где легко ошибиться
Размер когорты нужен как знаменатель; без него retention превращается в голое число вернувшихся.
Самостоятельная проверка
Сколько клиентов в августовской когорте?
Ответ: Два клиента. Это следует из исходных строк и условия запроса.
