SQL Interview Prep · Урок

Построение матрицы удержания

Подсчёт активных пользователей по когорте и смещению периода для формирования таблицы удержания

«Построение матрицы удержания» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Что такое матрица удержания

Следующий шаг после определения когорты — знаменитая матрица удержания: строки соответствуют когортам, столбцы — смещениям по периодам (месяц 0, 1, 2 и так далее), а каждая ячейка показывает, сколько пользователей из этой когорты оставались активными через указанное число периодов.

Интервьюеры любят эту задачу, потому что она требует объединить назначение когорты, соединение с данными об активности, вычисление разницы между периодами и сводное представление. Это самый показательный запрос в продуктовой аналитике.

Два исходных набора

Вам понадобятся две вещи: период когорты каждого пользователя из предыдущего урока и запись о каждом активном периоде для каждого пользователя. Данные об активности берутся из той же таблицы событий, сведённой к гранулярности периода.

Спланируйте запрос так: сначала CTE с когортами, затем CTE с активностью, в котором перечислены месяцы активности каждого пользователя, а после этого соедините их.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Перечисление активных периодов

CTE с активностью отвечает на вопрос: «В какие месяцы был активен каждый пользователь?» Усеките каждое событие до месяца и удалите дубликаты с помощью DISTINCT или GROUP BY, чтобы пользователь, совершивший 40 действий в марте, дал одну строку за март.

Именно этот список периодов по каждому пользователю соединяется с когортой для измерения сохранения активности через разные интервалы.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Вычисление смещения по периоду

Суть матрицы — это номер периода: сколько месяцев прошло от начала когорты до конкретного периода активности? Вычтите месяц когорты из месяца активности.

В Postgres удобно посчитать количество полных месяцев между двумя датами. Переносимая формула умножает разницу годов на 12 и прибавляет разницу месяцев; многие системы также предлагают готовые функции. Смещение 0 означает собственный начальный месяц когорты.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Объединение когорты с активностью

Объедините CTE когорты с CTE активности по полю user_id. Каждая строка результата означает: этот пользователь, родившийся в когорте X, проявлял активность со смещением N. Подсчёт уникальных пользователей для каждой пары (когорта, смещение) представляет матрицу в длинном формате.

Поскольку каждый участник когорты активен в своём исходном месяце, значение для смещения 0 должно совпадать с размером когорты. Это встроенная проверка корректности.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

Таблица удержания в длинном формате

Добавьте вычисление смещения и выполните агрегацию. Теперь у Вас есть аккуратный результат в длинном формате: одна строка на каждую когорту и каждое смещение с количеством удержанных пользователей. Многие интервьюеры принимают такой результат напрямую, поскольку преобразование в сводную таблицу носит лишь косметический характер.

Обратите внимание: выражение смещения указано и в SELECT, и в GROUP BY, поскольку оно вычисляется, а не хранится в отдельном столбце.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Преобразование в широкие столбцы

Чтобы получить классическую сетку, преобразуйте смещения в столбцы с помощью условной агрегации: используйте SUM с условием CASE для каждого смещения. Этот переносимый шаблон работает в любом диалекте без специального синтаксиса PIVOT.

Каждый CASE выдаёт 1, когда номер периода в строке совпадает с номером этого столбца, поэтому SUM подсчитывает удержанных пользователей для данного смещения.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

От подсчётов к показателям удержания

Обычно интервьюерам нужны проценты, а не исходные подсчёты. Разделите число удержанных пользователей для каждого смещения на размер когорты (смещение 0). Преобразуйте тип в число с плавающей точкой или умножьте значение на 1.0, чтобы избежать целочисленного деления — самой распространённой незаметной ошибки в этой задаче.

В результате получится кривая удержания: 100% в месяце 0 с постепенным снижением к плато. Именно это плато представляет собой показатель, который действительно интересует заинтересованные стороны.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

Ловушка целочисленного деления

Гарантированная каверзная деталь на собеседовании: в большинстве систем 120 / 500 равно 0, а не 0.24, поскольку оба операнда являются целыми числами. Проценты удержания незаметно превращаются в одни нули.

Исправьте это, сделав одну из сторон числовой: умножьте её на 100.0, преобразуйте один операнд с помощью CAST в NUMERIC или разделите на NULLIF(size, 0), чтобы заодно обработать пустую когорту. Если Вы скажете, что NULLIF предотвращает деление на ноль, это принесёт дополнительные баллы.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Заполнение пропущенных смещений нулями

Если у когорты не было удержанных пользователей при смещении 2, JOIN не создаст строку, и в матрице появится пробел. Чтобы явно показать 0, сформируйте полную сетку сочетаний (когорта, смещение), а затем присоедините к ней подсчёты с помощью LEFT JOIN.

Постройте сетку, выполнив CROSS JOIN когорт со списком чисел или смещений, затем замените пропущенные подсчёты нулём. Интервьюеры оценят, если Вы заметите этот пробел.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Треугольная форма и смещение в сторону новых когорт

Есть ещё один важный момент для обсуждения: матрица имеет треугольную форму. Когорта, начавшая пользоваться продуктом в прошлом месяце, ещё не может иметь значение для 3-го месяца, поэтому на поздних смещениях участвует меньше когорт.

Таким образом, сравнение среднего значения столбца по когортам смещено в пользу более старых когорт. Упомяните, что Вы либо честно покажете треугольную форму, либо ограничите сравнение теми смещениями, которых достигли все когорты. Такое понимание отличает аналитика от автора запросов.

Быстрая проверка

Ваш запрос удержания делит число удержанных пользователей на размер когорты, но каждый процент, кроме месяца 0, выводится как 0. Какова наиболее вероятная причина?

Итоги: матрица удержания

Чтобы построить матрицу удержания на собеседовании:

Назначьте каждому пользователю период когорты, а затем составьте список его периодов активности, удалив дубликаты.
Объедините эти данные и вычислите смещение периода — число месяцев между когортой и активностью.
Выполните агрегацию в длинном формате с помощью COUNT(DISTINCT user_id); при необходимости постройте сетку через CASE.
Осторожно преобразуйте подсчёты в показатели, избегая целочисленного деления и деления на ноль с помощью 100.0 и NULLIF.
Присоедините сгенерированную сетку с помощью LEFT JOIN, чтобы заполнить ячейки нулями, и помните, что матрица имеет треугольную форму.

Часто задаваемые вопросы

Урок «Построение матрицы удержания» бесплатный?

Да — полный текст урока «Построение матрицы удержания» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Построение матрицы удержания»?

Подсчёт активных пользователей по когорте и смещению периода для формирования таблицы удержания Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Построение матрицы удержания»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Определение когорты по первому действию
Построение матрицы удержания
Удержание на N-й день и скользящее удержание
Запросы для оттока и возвращения пользователей

← Назад к SQL Interview Prep