Retention M1 по когортам регистрации

Условие задачи

В users — дата регистрации каждого пользователя, в purchases — все его покупки. Постройте когорты по месяцу регистрации и для каждой когорты посчитайте: размер когорты, сколько пользователей вернулись (совершили покупку) в СЛЕДУЮЩИЙ календарный месяц после месяца регистрации, и retention в процентах (1 знак). Подвох: вернувшимся считается тот, у кого есть покупка именно в месяце регистрации + 1, и считать надо уникальных пользователей. Отсортируйте по месяцу когорты.

Схема данных

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  signup_date TEXT
);
CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  user_id INTEGER,
  purchase_date TEXT,
  amount INTEGER
);

Темы

retention cohort cte strftime distinct

Подсказки

• Месяц когорты: strftime('%Y-%m', signup_date). Следующий месяц: date(signup_date, 'start of month', '+1 month').
• Вернувшийся — это уникальный user, у которого есть покупка в next_month. Используйте DISTINCT, чтобы не задвоить пользователей с двумя покупками.
• LEFT JOIN когорты с подмножеством вернувшихся, чтобы пустые когорты не выпали; retention = 100.0 * retained / size.

Что проверяет эта задача

Задача «Retention M1 по когортам регистрации» учит считать удержание (retention) когортным методом на чистом SQL. Такие задачи встречаются на собеседованиях в Додо Пицца и других IT-компаниях. Уровень сложный — задача проверяет глубину и умение собрать запрос из нескольких приёмов.

Как подступиться к решению

Сначала разберись со схемой данных и пойми, какой результат нужен на выходе. Определи, какие таблицы и поля задействованы, и собирай запрос пошагово: фильтрация → соединение или группировка → итоговый отбор и сортировка. Проверь результат на крайних случаях — NULL, дубликаты, пустые группы. Свой запрос сразу прогоняешь автопроверкой в интерактивной SQL-песочнице на реальной базе (SQLite и PostgreSQL 16 прямо в браузере).

Разбор темы с примерами — в гайде «Retention-анализ в SQL». Хочешь системно с нуля — пройди бесплатный курс «SQL с нуля».

Решить в интерактивной SQL-песочнице → ← Все SQL-задачи