Когортный retention следующего месяца

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

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

Схема данных

CREATE TABLE users (
  user_id      INTEGER PRIMARY KEY,
  signup_date  TEXT NOT NULL
);
CREATE TABLE orders (
  order_id     INTEGER PRIMARY KEY,
  user_id      INTEGER NOT NULL,
  order_date   TEXT NOT NULL,
  amount       REAL NOT NULL
);

Темы

retention cohort cte strftime distinct left-join

Подсказки

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

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

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

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

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

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

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