SQL Studio / Разборы SQL / Воронка заказов
Воронка created → paid → delivered отвечает на вопрос «сколько заказов достигло каждого этапа», а не «какой статус у них сейчас». Для когорты заказов, созданных в ноябре, числитель оплаты — уникальные заказы с событием paid после created; знаменатель — все созданные заказы. Доставка засчитывается, только если событие delivered было после оплаты. Один заказ учитывается на каждом этапе максимум один раз.
Срез фиксируем явно: created_at с 1 ноября включительно до 1 декабря 2026 года; в первом кейсе вся история событий уже известна к моменту отчёта. Статусы — именно события переходов, а не одна строка текущего состояния. Это другое измерение, чем конверсия посетителя в покупателя или повторные покупки клиента.
Ошибка: подменить историю последним статусом
Представим шесть заказов: №101 оплачен и доставлен, №102 оплачен и позже отменён, №103 только создан, №104 ошибочно отмечен доставленным без оплаты, у №105 доставка зарегистрирована раньше оплаты, №106 содержит два одинаковых события paid. Все события и их время указаны прямо в CTE.
Соблазнительный запрос берёт последний статус на заказ и выдаёт delivered за «оплачено». Заказ, который уже доставлен, больше не будет иметь текущий статус paid. А отмена после оплаты не стирает исторический факт перехода.
WITH events(order_id, event_id, state, event_at) AS (
VALUES (101,1,'created','2026-11-02 10:00:00'),
(101,2,'paid','2026-11-02 10:05:00'),
(101,3,'delivered','2026-11-02 11:00:00'),
(102,4,'created','2026-11-02 10:10:00'),
(102,5,'paid','2026-11-02 10:20:00'),
(102,6,'cancelled','2026-11-02 11:00:00'),
(103,7,'created','2026-11-02 10:30:00'),
(104,8,'created','2026-11-02 10:40:00'),
(104,9,'delivered','2026-11-02 11:30:00'),
(105,10,'created','2026-11-02 10:50:00'),
(105,11,'delivered','2026-11-02 10:55:00'),
(105,12,'paid','2026-11-02 11:00:00'),
(106,13,'created','2026-11-02 11:00:00'),
(106,14,'paid','2026-11-02 11:10:00'),
(106,15,'paid','2026-11-02 11:10:00'),
(106,16,'delivered','2026-11-02 11:20:00')
),
latest AS (
SELECT order_id, state,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY event_at DESC,event_id DESC) AS rn
FROM events
)
SELECT SUM(CASE WHEN state='delivered' THEN 1 ELSE 0 END) AS paid_wrong,
SUM(CASE WHEN state='paid' THEN 1 ELSE 0 END) AS paid_now
FROM latest WHERE rn=1;
| paid_wrong | paid_now |
|---|---|
| 3 | 1 |
Если эти три заказа поделить на шесть созданных, получится 50% «конверсии в оплату». Но №104 не имеет оплаты, а №102 оплату проходил, хотя теперь отменён. Число текущих статусов paid — всего 1, и это тоже не число когда-либо оплаченных.
Правильная воронка: время перехода и один заказ на строку
Сведём события к одному набору дат на order_id. MIN выбирает первую регистрацию каждого этапа, а повторное событие оплаты у №106 не увеличивает счётчик. Для этих учебных событий есть максимум одна доставка на заказ; в реальном потоке с повторами и исправлениями понадобится более строгий поиск допустимой последовательности событий, а не три независимых MIN.
WITH events(order_id, event_id, state, event_at) AS (
VALUES (101,1,'created','2026-11-02 10:00:00'),
(101,2,'paid','2026-11-02 10:05:00'),
(101,3,'delivered','2026-11-02 11:00:00'),
(102,4,'created','2026-11-02 10:10:00'),
(102,5,'paid','2026-11-02 10:20:00'),
(102,6,'cancelled','2026-11-02 11:00:00'),
(103,7,'created','2026-11-02 10:30:00'),
(104,8,'created','2026-11-02 10:40:00'),
(104,9,'delivered','2026-11-02 11:30:00'),
(105,10,'created','2026-11-02 10:50:00'),
(105,11,'delivered','2026-11-02 10:55:00'),
(105,12,'paid','2026-11-02 11:00:00'),
(106,13,'created','2026-11-02 11:00:00'),
(106,14,'paid','2026-11-02 11:10:00'),
(106,15,'paid','2026-11-02 11:10:00'),
(106,16,'delivered','2026-11-02 11:20:00')
),
times AS (
SELECT order_id,
MIN(CASE WHEN state='created' THEN event_at END) AS created_at,
MIN(CASE WHEN state='paid' THEN event_at END) AS paid_at,
MIN(CASE WHEN state='delivered' THEN event_at END) AS delivered_at
FROM events GROUP BY order_id
),
stages AS (
SELECT order_id,
CASE WHEN paid_at>=created_at THEN 1 ELSE 0 END AS reached_paid,
CASE WHEN paid_at>=created_at AND delivered_at>=paid_at THEN 1 ELSE 0 END AS reached_delivered
FROM times
WHERE created_at>='2026-11-01' AND created_at<'2026-12-01'
)
SELECT COUNT(*) AS created_orders,
SUM(reached_paid) AS paid_orders,
SUM(reached_delivered) AS delivered_orders,
ROUND(100.0*SUM(reached_paid)/NULLIF(COUNT(*),0),2) AS paid_pct,
ROUND(100.0*SUM(reached_delivered)/NULLIF(COUNT(*),0),2) AS delivered_pct,
ROUND(100.0*SUM(reached_delivered)/NULLIF(SUM(reached_paid),0),2) AS delivered_from_paid_pct
FROM stages;
| created_orders | paid_orders | delivered_orders | paid_pct | delivered_pct | delivered_from_paid_pct |
|---|---|---|---|---|---|
| 6 | 4 | 2 | 66.67 | 33.33 | 50 |
Оплаты достигли заказы 101, 102, 105 и 106; доставку после оплаты — только 101 и 106. Значит, 4/6 = 66,67% созданных заказов достигли оплаты, 2/6 = 33,33% — корректной доставки, а 2/4 = 50% оплативших достигли доставки. Ошибки порядка №104 и №105 не надо молча засчитывать: они повод проверить источник событий.
Пограничный случай: заказ создан в ноябре, оплачен в декабре
Обрезать обе таблицы событий концом ноября — значит потерять оплату, пришедшую через двадцать минут после полуночи. В когортной воронке период относится к созданию заказа, а для оплаты задают самостоятельное окно наблюдения. Здесь оно заканчивается 3 декабря (исключительно).
WITH events(order_id,state,event_at) AS (
VALUES (201,'created','2026-11-30 23:50:00'),
(201,'paid','2026-12-01 00:10:00'),
(202,'created','2026-11-30 23:55:00')
), times AS (
SELECT order_id,
MIN(CASE WHEN state='created' THEN event_at END) AS created_at,
MIN(CASE WHEN state='paid' THEN event_at END) AS paid_at
FROM events GROUP BY order_id
)
SELECT COUNT(*) AS november_created,
SUM(CASE WHEN paid_at>=created_at AND paid_at<'2026-12-03' THEN 1 ELSE 0 END) AS paid_by_dec_03
FROM times WHERE created_at>='2026-11-01' AND created_at<'2026-12-01';
| november_created | paid_by_dec_03 |
|---|---|
| 2 | 1 |
Получаем 2 созданных и 1 оплаченный, то есть 50% по этому отдельному примеру. Запрос, который берёт только ноябрьские события оплаты, ошибочно вернул бы 0. Для реального отчёта также нужны согласованная временная зона, защита от поздних событий и одинаковая зрелость когорт.
Упражнение на последовательность
Уберите дублирующую оплату №106 и сначала предскажите результат: счётчик paid_orders не изменится. Затем исправьте событие доставки №105 на время 2026-11-02 11:05:00: сколько станет delivered_orders и конверсия среди оплативших? Попробуйте в бесплатной SQLite-песочнице, а в тренажёре — P041: заказы по состояниям. Эта задача считает статусы, а не историю прохождения: объясните себе разницу. Следующий шаг — P119 «Воронка оплат», где нужно убрать повторные успешные платежи на один заказ; она тоже не заменяет полноценную воронку событий.
Документация SQLite: WITH и CTE и оконные функции.
Для тех, кто хочет идти дальше по программе на MySQL 8 (все примеры выше проверены в SQLite), есть отдельный курс на Stepik. Бесплатные разборы и тренажёр доступны независимо от него.