Условие
Таблица events(user_id, event_type, event_time). Постройте строгую воронку из 4 шагов: landing → signup → onboarding → first_action. Пользователь считается «прошёл шаг N», только если он:
- Прошёл все предыдущие шаги по порядку,
- Шаг N произошёл после шага N-1 по времени.
Верните: step, users, drop_off, conv_from_step1, conv_from_prev.
Решение
WITH step_times AS (
SELECT
user_id,
MIN(CASE WHEN event_type = 'landing' THEN event_time END) AS t1,
MIN(CASE WHEN event_type = 'signup' THEN event_time END) AS t2,
MIN(CASE WHEN event_type = 'onboarding' THEN event_time END) AS t3,
MIN(CASE WHEN event_type = 'first_action' THEN event_time END) AS t4
FROM events
GROUP BY user_id
),
passed AS (
SELECT
user_id,
CASE WHEN t1 IS NOT NULL THEN 1 ELSE 0 END AS s1,
CASE WHEN t1 IS NOT NULL AND t2 > t1 THEN 1 ELSE 0 END AS s2,
CASE WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2 THEN 1 ELSE 0 END AS s3,
CASE WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AND t4 > t3 THEN 1 ELSE 0 END AS s4
FROM step_times
),
counts AS (
SELECT
SUM(s1) AS step1, SUM(s2) AS step2, SUM(s3) AS step3, SUM(s4) AS step4
FROM passed
)
SELECT *
FROM (
SELECT 1 AS step_no, 'landing' AS step, step1 AS users,
NULL::INT AS drop_off,
1.0 AS conv_from_step1,
NULL::NUMERIC AS conv_from_prev FROM counts
UNION ALL
SELECT 2, 'signup', step2,
step1 - step2,
step2::NUMERIC/NULLIF(step1,0),
step2::NUMERIC/NULLIF(step1,0) FROM counts
UNION ALL
SELECT 3, 'onboarding', step3,
step2 - step3,
step3::NUMERIC/NULLIF(step1,0),
step3::NUMERIC/NULLIF(step2,0) FROM counts
UNION ALL
SELECT 4, 'first_action', step4,
step3 - step4,
step4::NUMERIC/NULLIF(step1,0),
step4::NUMERIC/NULLIF(step3,0) FROM counts
) t
ORDER BY step_no;Идея
- Сворачиваем все события юзера в одну строку с временами 4 шагов.
- Проверяем строгий порядок:
t2 > t1 > ...— каждый шаг должен быть позже предыдущего. - UNION для разворота в «длинный» формат.
Альтернатива через LAG
Можно посчитать сразу как «у каждого юзера найти максимальный последовательный шаг» через ROW_NUMBER по event_time + проверка порядка. Менее читабельно.
Подводные камни
MIN(CASE...)— первое вхождение. Если у юзера несколькоsignup(что странно, но бывает), берём первый.- Строгий
>, не>=. Если события в одну миллисекунду — порядок неопределён. Можно>= t1 AND tie-break по event_id. - Юзеры с шагом N без шага N-1. В строгой воронке не считаются. В «нестрогой» — считались бы. Объяснить на собесе.
- Conversion при step1 = 0. Деление на 0 →
NULLIF(step1, 0)спасает.
Эталонный ответ
Свернуть события в одну строку через MIN(CASE WHEN type=...), проверить строгое нарастание времён, UNION ALL в длинный формат для отчёта. Этот паттерн — backbone воронок в любом продукте.