Месячный retention M1 по когортам покупок

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

По таблице purchases (покупки пользователей) построй когортный retention M1. Когорта — месяц первой покупки пользователя (формат YYYY-MM). Для каждой когорты выведи колонки: cohort_month — месяц когорты; cohort_size — число пользователей в когорте; retained_m1 — сколько из них совершили хотя бы одну покупку в СЛЕДУЮЩЕМ календарном месяце после месяца первой покупки; retention_pct — доля вернувшихся в M1, округлённая до 1 знака. Важно: возврат через месяц (в M2), минуя M1, за retention M1 НЕ засчитывается. Отсортируй по cohort_month по возрастанию.

Схема данных

CREATE TABLE purchases (
  user_id INTEGER NOT NULL,
  purchase_date TEXT NOT NULL,   -- YYYY-MM-DD
  amount INTEGER NOT NULL
);

Темы

retention когорты cohort-analysis strftime group-by

Подсказки

• Когорта = strftime('%Y-%m', MIN(purchase_date)) по каждому user_id.
• «Следующий месяц» надёжнее считать от даты: date(first_date,'start of month','+1 month'), а не прибавлять 1 к номеру месяца — иначе сломается переход через декабрь.
• Возврат в M2, минуя M1, за retention M1 не считается: сравнивай месяц покупки строго с соседним месяцем когорты, а не 'позже первой покупки'.

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

Задача «Месячный retention M1 по когортам покупок» учит решать задачи по теме «Когорты и retention» так, как это спрашивают на собеседовании аналитика данных. Такие задачи встречаются на собеседованиях в Ozon и других IT-компаниях. Уровень сложный — задача проверяет глубину и умение собрать запрос из нескольких приёмов.

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

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

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

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