Coding Interview Prep · Урок

Упорядоченные события и временные окна

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

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

Почему важны порядок и время

Базовая воронка из предыдущего урока проверяет только, выполнял ли пользователь каждый шаг. Более внимательный интервьюер спросит: произошли ли шаги в правильном порядке и за разумное время?

Пользователь, который совершил покупку в понедельник, а посетил маркетинговую страницу в пятницу, не прошёл вашу воронку. Последовательность и время превращают наивную воронку на основе флагов в достоверную.

Идея первых временных меток для каждого пользователя

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

Тогда корректная конверсия означает, что first_signup_time >= first_visit_time, и так далее по цепочке. Сгруппированное по шагу выражение MIN(event_time) даёт нужные опорные значения.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

Требование последовательного прохождения шагов

Если для каждого шага известна первая временная метка, порядок можно обеспечить сравнением. Пользователь действительно перешёл на шаг 3, только если каждая временная метка не равна NULL и значения монотонно возрастают.

Обратите внимание: временная метка NULL (шаг никогда не выполнялся) естественным образом не проходит сравнение, что именно и требуется.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

Добавление временного окна

У большинства воронок есть крайний срок: «завершить конверсию в течение 7 дней после первого посещения». Добавьте ограничение интервала между первым и последним шагом.

Арифметика дат зависит от диалекта. В Postgres можно написать visit_t + INTERVAL '7 days', а в MySQL использовать DATE_ADD(visit_t, INTERVAL 7 DAY). Всегда указывайте используемый диалект.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

Почему важна первая временная метка, а не любая

Тонкий момент на собеседовании: должно ли окно отсчитываться от первого посещения пользователя или от его последнего посещения перед регистрацией? Это зависит от сути вопроса о продукте.

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

Спросите интервьюера, какой вариант он имеет в виду: осознанный выбор показывает высокий уровень.

Упорядоченные события с помощью LEAD

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

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

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

Проверка следующего ожидаемого шага

Развивая идею с LEAD, оставьте строки, в которых за «посещением» непосредственно следует «регистрация». Так вы найдёте настоящие последовательные переходы, а не просто совместное появление событий.

Можно объединить такие проверки переходов в цепочку и проверить весь упорядоченный путь шаг за шагом.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

Время между последовательными шагами

Интервьюеры любят спрашивать: «Сколько времени занимает каждый шаг?» Используйте LEAD для временной метки следующего события и вычтите одну временную метку из другой. Разница между соседними событиями — это время пребывания на данном этапе.

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

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

Случай одинаковых временных меток

Что если два события имеют одинаковое значение event_time? Тогда signup_t >= visit_t истинно, даже если события произошли одновременно, а одного упорядочивания по времени недостаточно.

Осознанно выбирайте между >= и > и объясняйте почему.
Добавьте дополнительный ключ, например идентификатор последовательности событий, в ORDER BY, чтобы результаты оконных функций были детерминированными.

Если упомянуть это без подсказки, интервьюеры будут впечатлены.

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

Объединение порядка и временного окна в одном запросе

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

Такой ответ отличает кандидата, который понимает воронки, от кандидата, который умеет только считать флаги.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

Примечания о различиях диалектов

Два напоминания о переносимости при написании SQL в реальном времени:

FILTER (WHERE ...) для агрегатных функций предусмотрен стандартом SQL и работает в Postgres; в MySQL или старых СУБД используйте запасной вариант SUM(CASE WHEN ... THEN 1 ELSE 0 END).
Синтаксис интервалов различается: Postgres — + INTERVAL '7 days', MySQL — DATE_ADD(d, INTERVAL 7 DAY), SQL Server — DATEADD(day, 7, d).

Озвучьте своё допущение: интервьюеру обычно неважно, какой диалект вы выбрали, важно лишь, что вы знаете о различиях.

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

Вы должны посчитать пользователей, которые последовательно прошли этапы посещения -> регистрации -> покупки в течение 7 дней с первого посещения. Какой подход правильный?

Итоги: упорядоченные события и временные окна

Основные выводы:

Фиксируйте для каждого пользователя первую временную отметку каждого этапа с помощью MIN(CASE ...).
Обеспечивайте последовательность, требуя, чтобы время каждого этапа было не раньше времени предыдущего этапа.
Ограничивайте путь интервалом, указывая синтаксис, принятый в Вашем диалекте.
Используйте LEAD/LAG для проверки переходов и времени между этапами.
Обрабатывайте совпадения временных отметок с помощью дополнительного критерия в ORDER BY.

Далее: переход от воронок к экспериментам и вычисление метрик для каждого варианта.

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

Урок «Упорядоченные события и временные окна» бесплатный?

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

Чему я научусь в уроке «Упорядоченные события и временные окна»?

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

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

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

Сколько времени занимает урок «Упорядоченные события и временные окна»?

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

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

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

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

Построение многоэтапной воронки
Упорядоченные события и временные окна
Распределение по группам A/B-теста и метрики
Прирост, значимость и контрольные показатели в SQL

← Назад к Coding Interview Prep