Определить активных пользователей по когортам подписки (rolling retention)

Условие задания

## Контекст

Вы аналитик финтех-сервиса с ежемесячной подпиской (как банковское premium-подписочное приложение). Продукт-менеджер просит построить **месячный retention по когортам**: для пользователей, впервые оплативших в месяц X, какая доля из них совершила хотя бы один платёж в месяц X, X+1, X+2, ... (rolling retention по месяцу лайфтайма).

## Данные

Таблица `payments`:

| Колонка | Тип | Описание |
|---|---|---|
| `payment_id` | BIGINT | ID платежа (PK) |
| `user_id` | BIGINT | ID пользователя |
| `paid_at` | DATE | Дата успешного списания |
| `amount` | NUMERIC(10,2) | Сумма, руб |

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

## Задание

1. Определите **когорту** каждого пользователя — месяц его **первого** платежа (`cohort_month`, первое число месяца).
2. Для каждого платежа посчитайте **month_number** — сколько полных месяцев прошло от когортного месяца до месяца платежа (0 = месяц регистрации/первого платежа, 1 = следующий месяц, и т.д.).
3. Постройте таблицу retention: для каждой пары (`cohort_month`, `month_number`) число уникальных активных пользователей и их доля от размера когорты.

**Формат ответа:** `cohort_month`, `cohort_size`, `month_number`, `active_users`, `retention_pct` (доля активных от размера когорты, %, округлить до 1 знака). Отсортировать по `cohort_month`, `month_number`.

## Уточнение

- Размер когорты (`cohort_size`) — число уникальных пользователей, впервые заплативших в этом месяце. Он один и тот же для всех `month_number` внутри когорты.
- Если в каком-то `month_number` активных нет, строку можно не выводить (заполнять нулями не требуется).

Темы

retention cohort generate_series LEFT JOIN CTE date arithmetic

Подсказки

• Первый платёж пользователя = MIN(paid_at) в разрезе user_id; отсюда получаете cohort_month через date_trunc.
• month_number считается как разница месяцев: (год_платежа*12 + месяц_платежа) - (год_когорты*12 + месяц_когорты).
• cohort_size удобно посчитать отдельным CTE как COUNT(DISTINCT user_id) на когорту, а потом приджойнить к сводке активности.
• Активность в (cohort_month, month_number) — это COUNT(DISTINCT user_id), потому что у пользователя в месяце может быть несколько платежей.

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Определить активных пользователей по когортам подписки (rolling retention)"?

Это задание для уровня hard. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: retention, cohort, generate_series, LEFT JOIN, CTE.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 520+ SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания