0Pricing
Coding Interview Prep · Урок

Определение когорты по первому действию

Назначение каждому пользователю когорты на основе даты его первого события

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

Почему на собеседованиях спрашивают о когортах

Когда интервьюер по продуктовой аналитике говорит «постройте когорту», он проверяет, умеете ли Вы распределить каждого пользователя в группу по признаку времени, когда пользователь впервые что-то сделал, а затем отслеживать эту группу во времени.

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

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

Исходная таблица

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

  • user_id — кто выполнил действие
  • event_type — что было сделано
  • event_at — когда это произошло, в виде метки времени

На собеседовании вслух уточните гранулярность: «Здесь одна строка соответствует одному событию, и пользователь может встречаться много раз?» Ответ почти всегда будет положительным, и именно поэтому нужна агрегация, чтобы свести данные к первому действию каждого пользователя.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

Первое действие = MIN временной метки

Основной шаг прост: сгруппируйте данные по user_id и возьмите MIN(event_at). Это минимальное значение и есть первое действие пользователя — момент, который определяет его когорту.

Именно это интервьюеры хотят услышать в первую очередь, ещё до обсуждения оконных функций. Обычный GROUP BY корректен, понятен и быстр.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Фильтрация по определяющему событию

Часто когорта определяется конкретным действием, а не любым событием. Фраза «сформировать когорты пользователей по их первой покупке» означает, что перед вычислением минимума нужно оставить только строки о покупках.

Поместите фильтр в WHERE, чтобы MIN видел только подходящие строки. Распространённая ошибка на собеседовании — вычислить MIN по всем событиям, а затем отфильтровать результат. В таком случае пользователю, который сначала просматривал товары, а потом совершил покупку, будет назначена неверная дата начала.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Распределение по периоду когорты

Когорта обычно представляет собой период, а не точную метку времени: например, «когорта 2024-03» или «неделя, начинающаяся 2024-03-04». Усеките дату первого действия до нужной гранулярности периода.

В Postgres используйте DATE_TRUNC('month', ...). В MySQL можно использовать DATE_FORMAT(d, '%Y-%m-01'); в SQL Server — DATETRUNC(month, d) или вычисляемое начало месяца. На собеседовании назовите используемый диалект, чтобы выбор синтаксиса выглядел осознанным.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Объединение в CTE

Назначение когорты каждому пользователю — это строительный блок, который Вы будете повторно использовать в запросах для анализа удержания, поэтому оформите его в чётко названный CTE. Это делает следующие шаги понятнее и показывает интервьюеру, что Вы мыслите составными частями.

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

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

Размер когорты: подсчёт участников

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

Для надёжности используйте COUNT(DISTINCT user_id), даже если в CTE уже есть одна строка на пользователя: это показывает, что Вы учитываете гранулярность данных. Позже это число станет знаменателем для каждого процента удержания.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Альтернатива с оконной функцией

Иногда интервьюеры просят прикрепить метку когорты к каждой строке события, а не получить сводную таблицу. Здесь особенно полезна оконная функция: MIN(event_at) OVER (PARTITION BY user_id) вычисляет первое действие, не удаляя строки.

Это удобно, когда в одном проходе нужно сохранить и подробные события, и метку когорты — именно так подготавливаются данные для подсчёта удержания.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

Проблема совпадающих значений и дубликатов

Что произойдёт, если у пользователя два события имеют одинаковую самую раннюю метку времени? MIN корректно обрабатывает этот случай: он возвращает одно минимальное значение независимо от количества совпадающих строк, поэтому назначение когорты остаётся единственным для каждого пользователя.

Сравните это с подходом ROW_NUMBER() ... ORDER BY event_at: при совпадении значений строки выбираются произвольно, поэтому для стабильного результата нужно добавить детерминированный критерий, например event_id. Если Вы сами упомянете этот компромисс, это будет выглядеть как ответ опытного специалиста.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Часовые зоны и граница дня

Тонкая проверка на собеседовании: покупка в 11:30 PM в Нью-Йорке приходится на следующий день по UTC. Если когорты распределяются по календарным дням, именно часовая зона определяет, в какую когорту попадёт пользователь.

Безопасный ответ: храните метки времени в UTC, а затем преобразуйте их в часовую зону бизнеса до усечения. Явно скажите, какая зона определяет «день» для метрики, потому что одно это решение может переместить между когортами тысячи пользователей.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Исключение пользователей, пришедших до начала окна

В реальном анализе период когорты ограничивают диапазоном дат, например «когорты, начавшиеся в первом квартале». Фильтруйте по агрегированной дате первого действия — это означает использовать предложение HAVING или внешний фильтр для CTE, а не WHERE для исходных событий.

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

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Проверка

Интервьюер спрашивает: «Сформируйте когорту каждого пользователя по месяцу первой покупки. До покупки пользователи могли просматривать товары». Какой подход правильный?

Итоги: определение когорты

Главные выводы для вопроса на собеседовании об определении когорты:

  • Когорта объединяет пользователей по их первому подходящему действию.
  • Вычисляйте его с помощью MIN(event_at) после фильтрации по определяющему событию в WHERE.
  • Распределяйте пользователей по периоду с помощью DATE_TRUNC или эквивалентного средства выбранного диалекта.
  • Оформляйте назначение в CTE для повторного использования; COUNT(DISTINCT user_id) даёт размер когорты.
  • Учитывайте границу дня в часовой зоне и ограничивайте диапазоны дат по вычисленному первому действию, а не по исходным событиям.

Освойте это, и матрица удержания в следующем уроке сведётся к соединению таблиц.

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

Урок «Определение когорты по первому действию» бесплатный?

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

Чему я научусь в уроке «Определение когорты по первому действию»?

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

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

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

Сколько времени занимает урок «Определение когорты по первому действию»?

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

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

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

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

  1. Определение когорты по первому действию
  2. Построение матрицы удержания
  3. Удержание на N-й день и скользящее удержание
  4. Запросы для оттока и возвращения пользователей
← Назад к Coding Interview Prep