Cohort retention матрица

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

Таблицы users(id, signup_month) и activity(user_id, active_month). Постройте cohort retention: для каждой signup-когорты — % юзеров, активных в month_offset 0, 1, 2 (где 0 = месяц регистрации). Выведите cohort, m0, m1, m2 (округлить до 1 знака). Сортировка по cohort. signup_month и active_month в формате YYYY-MM.

Схема данных

CREATE TABLE users (id INTEGER PRIMARY KEY, signup_month TEXT);
CREATE TABLE activity (user_id INTEGER, active_month TEXT);

Темы

CTE JOIN CASE WHEN SUM COUNT pivot cohort

Подсказки

• Сначала размер каждой когорты
• Считаем offset = active_month − signup_month в месяцах
• Pivot по offset 0,1,2 через SUM(CASE)

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

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

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

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

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

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