Cohort retention: Day-N retention матрица по месяцам регистрации

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

**Контекст:** Классическая задача для продуктового аналитика. Нужна когортная матрица: по строкам — месяц регистрации, по столбцам — Day-0/7/14/30/60/90 retention.

**Данные:**

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

**Задание:** построить таблицу

| cohort_month | cohort_size | d0 | d7 | d14 | d30 | d60 | d90 |
|---|---|---|---|---|---|---|---|
| 2026-01 | 12500 | 100% | 42% | 28% | 18% | 14% | 11% |
| 2026-02 | 14000 | 100% | 45% | 30% | 20% | 16% | 13% |
| ... | | | | | | | |

**Правило Day-N retention:** юзер «удержался» в Day-N, если сделал хотя бы одно событие **в окне [Day-N, Day-N+1)**, считая от даты регистрации.

Для когорт последних 6 месяцев.

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

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

CREATE TABLE users (
  user_id       BIGINT PRIMARY KEY,
  created_at    TIMESTAMP NOT NULL
);

CREATE TABLE events (
  event_id      BIGINT PRIMARY KEY,
  user_id       BIGINT NOT NULL,
  ts            TIMESTAMP NOT NULL
  -- любая активность — заход, клик, что угодно
);

Темы

cohort retention window-functions DATE_TRUNC

Подсказки

• Считать по дням от регистрации: FLOOR(EXTRACT(EPOCH FROM (ts - created_at)) / 86400)
• Cohort month = DATE_TRUNC('month', created_at)
• Для точечного Day-N — флаг MAX(CASE WHEN day_num = 7 THEN 1 ELSE 0 END)
• Финальное COUNT(DISTINCT user_id) на уровне когорты, потом SUM(флаг) / COUNT(*)

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

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

Какой уровень знаний нужен для задачи "Cohort retention: Day-N retention матрица по месяцам регистрации"?

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

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

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

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

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

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

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

← Все задания