Построение многоэтапной воронки
Подсчёт пользователей, достигших каждого этапа по порядку, и вычисление коэффициентов конверсии между этапами
«Построение многоэтапной воронки» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.
Что на самом деле проверяет вопрос о воронке
Когда интервьюер говорит «постройте воронку регистрации», он проверяет, умеете ли вы посчитать уникальных пользователей, достигших каждого шага по порядку, и выразить отсеивание между шагами.
Воронка может состоять из таких этапов, как visit -> signup -> activate -> purchase. Обычно результатом должна быть одна строка на шаг с количеством пользователей и коэффициентом конверсии.
- Считайте пользователей, а не события: если один пользователь дважды выполнил событие, это всё равно один пользователь.
- Шаги идут в определённом порядке: достижение шага 3 означает, что пользователь прошёл шаги 1 и 2.
Таблица событий, которую вам предоставят
Почти в каждом вопросе о воронке вам предоставят одну таблицу событий в длинном формате. Представьте себе такую структуру:
user_id— кто выполнил действиеevent_name— например, «посещение», «регистрация», «покупка»event_time— временная метка
Одна строка соответствует одному действию. Ваша задача — преобразовать эти данные в пошаговый подсчёт. Перед написанием SQL всегда уточняйте у интервьюера точные названия событий.
CREATE TABLE events (
user_id INT,
event_name VARCHAR(50),
event_time TIMESTAMP
);Подсчёт пользователей на одном шаге
Начните с простого: сколько уникальных пользователей достигли одного шага? Используйте COUNT(DISTINCT user_id) с фильтром по названию события.
Это основа любой воронки. Если вы умеете аккуратно посчитать один шаг, то сможете посчитать и все остальные.
SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';Условная агрегация для всех шагов
Чёткий ответ на собеседовании — посчитать каждый шаг за один проход с помощью условной агрегации: использовать CASE внутри COUNT(DISTINCT ...).
Для каждого шага считайте уникальных пользователей, событие которых соответствует этому шагу. Один просмотр данных — одна строка с итогами по всем шагам.
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'visit' THEN user_id END) AS step1_visit,
COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END) AS step2_signup,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;Скрытая ошибка: шаги не упорядочены
В только что рассмотренном запросе есть ловушка, которую любят использовать интервьюеры. Он считает всех, кто выполнил событие «покупка», даже если в данных он никогда не посещал страницу и не регистрировался.
Настоящая воронка требует, чтобы каждый следующий шаг был подмножеством предыдущего. Независимый подсчёт событий может привести к тому, что шаг 3 окажется больше шага 2, а для воронки это логически невозможно.
Исправление: связать шаги каждого пользователя, обычно сначала свернув данные до одной строки на пользователя.
Одна строка на пользователя с флагами
Надёжный шаблон: свернуть журнал событий до одной строки на пользователя и добавить логический флаг (в виде 0 или 1), показывающий, выполнял ли пользователь каждый шаг. Конструкция MAX(CASE ...) превращает длинный журнал в широкую сводку по пользователям.
WITH user_steps AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS did_visit,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS did_signup,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id
)
SELECT * FROM user_steps;Обеспечение порядка шагов
Теперь обеспечьте выполнение правила воронки: пользователь учитывается на шаге N, только если он также выполнил все предшествующие шаги. Достижение этапа «покупка» имеет значение только в том случае, если пользователь также посещал страницу и регистрировался.
Складывайте флаги с одновременной проверкой обязательных условий, чтобы каждый шаг действительно был подмножеством предыдущего.
WITH user_steps AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS did_visit,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS did_signup,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id
)
SELECT
SUM(did_visit) AS step1_visit,
SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END) AS step2_signup,
SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END) AS step3_purchase
FROM user_steps;Преобразование подсчётов в аккуратный длинный результат
Интервьюеры часто предпочитают одну строку на каждый шаг, а не одну широкую строку. Преобразуйте широкие итоги в длинный формат с помощью небольшого UNION ALL, добавив номер шага для упорядочивания.
Так на следующем шаге будет гораздо проще вычислять коэффициенты конверсии и строить графики.
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;Конверсия от шага к шагу
Важны два коэффициента, и интервьюер спросит, какой именно вы имеете в виду:
- Конверсия шага: количество пользователей на этом шаге, делённое на количество пользователей на предыдущем шаге.
- Общая конверсия: количество пользователей на этом шаге, делённое на количество пользователей в начале воронки.
Используйте LAG, чтобы получить количество пользователей на предыдущем шаге и вычислить конверсию между шагами. Приведите значение к десятичному типу, чтобы избежать целочисленного деления.
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT
step_name,
users,
ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;Общая конверсия от верхней точки
Для общей конверсии делите количество пользователей на каждом шаге на количество пользователей на первом шаге. FIRST_VALUE для упорядоченной воронки фиксирует это начальное значение в каждой строке.
Всегда упоминайте интервьюеру, что вы исключили целочисленное деление, умножив значение на 100.0.
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT
step_name,
users,
ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;Собираем всю воронку
Вот полное решение от начала до конца, которого ждут интервьюеры: свернуть данные до флагов по пользователям, обеспечить порядок, преобразовать широкую форму в длинную, а затем вычислить оба коэффициента. Проговаривайте решение вслух, объясняя назначение каждого CTE.
Эта структура масштабируется: чтобы добавить шаг, достаточно добавить один флаг и одну строку UNION ALL.
WITH user_steps AS (
SELECT user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS s1,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS s2,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
FROM events GROUP BY user_id
),
totals AS (
SELECT 1 AS step_no, 'visit' AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
SELECT 2, 'signup', SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;Быстрая проверка
Интервьюер замечает, что ваша воронка показывает 95 пользователей на этапе «покупка», но только 80 на этапе «регистрация». Какова наиболее вероятная причина?
Итоги: многошаговые воронки
Теперь вы умеете строить воронку так, как ожидают на собеседованиях:
- Считайте уникальных пользователей на каждом упорядоченном шаге, а не необработанные события.
- Сворачивайте журнал событий до одной строки на пользователя с флагами
MAX(CASE ...). - Обеспечивайте порядок, чтобы каждый шаг был подмножеством предыдущего.
- Вычисляйте конверсию между шагами (LAG) и общую конверсию (FIRST_VALUE), не допуская целочисленного деления.
Далее: убедимся, что эти шаги действительно произошли в правильной последовательности и в пределах временного окна.
Часто задаваемые вопросы
Урок «Построение многоэтапной воронки» бесплатный?
Да — полный текст урока «Построение многоэтапной воронки» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 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 — локальная установка не требуется.
Все уроки этого курса
- Построение многоэтапной воронки
- Упорядоченные события и временные окна
- Распределение по группам A/B-теста и метрики
- Прирост, значимость и контрольные показатели в SQL