Когорты в SQL. 18 задач по удержанию

Зачем нужны когорты

Общий средний показатель часто смешивает старых и новых клиентов так, что становится непонятно, что реально улучшается.

Когорта фиксирует момент входа клиента и позволяет смотреть активацию, удержание и деньги во времени.

Эта книга тренирует SQL-запросы для когорт без внешних BI-инструментов и без личных данных.

Главный контроль

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

Различайте календарный месяц, точное число дней и возраст когорты: это разные правила.

Все примеры запускаются в SQLite на маленьких встроенных таблицах.

Задача 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 превращается в голое число вернувшихся.

Сколько клиентов в августовской когорте?

Ответ: Два клиента. Это следует из исходных строк и условия запроса.

Задача 3. Найти первую покупку в когорте

Нужно связать регистрацию и первую покупку, чтобы понять, кто активировался.

CREATE TABLE customers(client_id INTEGER, registered_at TEXT);

CREATE TABLE orders(client_id INTEGER, paid_at TEXT);

INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');

INSERT INTO orders VALUES (10,'2026-08-16'),(10,'2026-09-02'),(30,'2026-09-03');

SELECT c.client_id, substr(c.registered_at,1,7) AS cohort_month, MIN(o.paid_at) AS first_paid_at

FROM customers c

LEFT JOIN orders o ON o.client_id = c.client_id

GROUP BY c.client_id, cohort_month

ORDER BY c.client_id;

[[10, "2026-08", "2026-08-16"], [20, "2026-08", null], [30, "2026-09", "2026-09-03"]]

LEFT JOIN сохраняет клиентов без покупок, а MIN показывает первую оплату там, где она есть.

Запрос сделан на маленькой синтетической таблице, поэтому ответ можно проверить глазами и повторить в SQLite.

Явный ORDER BY или отдельная агрегация фиксируют смысл результата, а не оставляют его на случайный порядок строк.

INNER JOIN удалил бы неактивировавшихся клиентов и завысил бы картину активации.

У какого клиента нет first_paid_at? Объясните ответ по исходным строкам и условию запроса.

Ответ: У клиента 20. Это следует из исходных строк и условия запроса.

Задача 4. Посчитать активацию когорты

Нужно узнать долю клиентов когорты, у которых была хотя бы одна покупка.

CREATE TABLE customers(client_id INTEGER, registered_at TEXT);

CREATE TABLE orders(client_id INTEGER, paid_at TEXT);

INSERT INTO customers VALUES (10,'2026-08-15'),(20,'2026-08-20'),(30,'2026-09-01');

INSERT INTO orders VALUES (10,'2026-08-16'),(30,'2026-09-03');