Когортная таблица retention (матрица W0-W4)

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

**Контекст:** Задание с технического собеса на senior-аналитика. Нужно собрать **когортную таблицу retention по неделям** — базовый инструмент продуктовой аналитики.

**Данные:**

[см. код в задании]

**Определения:**
- **Когорта** = пользователи, зарегистрировавшиеся в одну неделю (`event_type = 'signup'`)
- **Активность** = любой `login` или `purchase` от этого пользователя в будущем
- **Retention Wk** = доля юзеров когорты, которые были активны **на k-й неделе** после регистрации

**Задание:**

Собрать таблицу вида:

| cohort_week | size | W0 | W1 | W2 | W3 | W4 |
|-------------|------|------|------|------|------|------|
| 2026-02-02 | 1200 | 100% | 42% | 28% | 22% | 20% |
| 2026-02-09 | 1400 | 100% | 45% | 30% | — | — |

- W0 всегда 100% (неделя регистрации).
- Если данных ещё нет (когорта слишком молодая для Wk) — NULL.

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE user_events (
  user_id    BIGINT,
  event_ts   TIMESTAMP,
  event_type VARCHAR(32)  -- 'signup', 'login', 'purchase', ...
);

Темы

cohort-analysis retention window-functions pivot DATE_TRUNC

Подсказки

• Два CTE: первый строит когорту (user → cohort_week), второй считает активность по неделям
• DATE_TRUNC('week', event_ts) приводит timestamp к началу недели
• Week offset = DATE_TRUNC(week, event_ts) - cohort_week, разделить на 7 дней
• Финальный pivot — через FILTER (WHERE ...) или CASE WHEN ...

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

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

Какой уровень знаний нужен для задачи "Когортная таблица retention (матрица W0-W4)"?

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

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

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

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

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

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

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

← Все задания