Собесов
← Все задачи

Aviasales Booking — SQL: исправить запрос по воронке оплаты

SQLРефакторинг и баги SQLСложнаяMiddle

Условие

Даны две таблицы:

  • orders — детали заказа, один заказ = одна строка, ключ order_id.
  • payment_funnel_events — события воронки оплаты. Тип события — event_type.

Дан запрос, в котором есть ошибки и местами неоптимальный код. К некоторым подзапросам приведены комментарии — верить нужно комментариям и подгонять SQL под них (не наоборот).

Перепишите запрос, перечислите изменения и обоснуйте их.

with last_error as (
  -- выбираем последнюю ошибку, которая произошла в рамках заказа (ошибки могут случаться не во всех заказах)
  select * from (
    select order_id, error_type,
           row_number() over(partition by order_id, user_id order by created_at asc) as rn
      from payment_funnel_events
      where event_type = 'payment_error--shown'
    ) s
  where rn = 1
),
 
payment_methods as (
  -- выбираем все использованные методы оплаты и время их последнего использования
  select order_id, payment_method, max(created_at) as last_changed
  from payment_funnel_events
  where event_type = 'payment_method--selected'
)
 
 
select od.order_id,
    od.payment_started, od.payment_finished,
    cast(round((unix_timestamp(payment_finished) - unix_timestamp(payment_started)) / 60, 0) as int) as payment_dur,
    coalesce(payment_page_submit, 0) payment_page_shown,
    coalesce(payment_page_submit, 0) payment_page_submit,
    coalesce(selected_card_payment, 0) selected_card_payment,
    coalesce(selected_sbp_payment, 0) selected_sbp_payment,
    coalesce(pm_max.payment_method, 'bank_card') as last_payment_method,
    error_type, od.pdate
from orders od
inner join (
    -- считаем количество показов и сабмитов страниц для каждого заказа
    select order_id,
      count(case when event_type = 'page--shown' then created_at else null end) as 'payment_page_shown',
      count(case when event_type = 'page--submit' then created_at else 0 end) as 'payment_page_submit'
    from payment_funnel_events
    where created_at > '10-01-2024'
    group by 1) pe
  on od.order_id = pe.order_id
inner join last_error le
  on od.order_id = le.order_id
left join (
  -- проверяем, какие методы оплаты были использованы и выбираем дату последней попытки оплаты
  select order_id,
    sum(case when payment_method = 'bank_card' then 1 else 0 end) as 'selected_card_payment',
    sum(case when payment_method = 'sbp' then 1 else 0 end) as 'selected_sbp_payment',
    max(last_changed) as max_last_changed
  from payment_methods
  group by 1) pm
  on od.order_id = pm.order_id
left join payment_methods pm_max
  on od.order_id = pm.order_id and pm.max_last_changed = pm_max.last_changed
where od.created_at > '01-01-2024'

Решение

Где ошибки

Идём по комментариям и проверяем код.

1. CTE last_error — «последняя ошибка», но взята первая

Комментарий: «выбираем последнюю ошибку». В коде:

row_number() over(partition by order_id, user_id order by created_at asc)
where rn = 1

asc + rn = 1 = первая ошибка. Чтобы взять последнюю — desc (или rn = count_over). Также partition by order_id, user_id избыточна и потенциально опасна: если в payment_funnel_events нет user_id (или null), партиции бьются неверно. Достаточно partition by order_id.

with last_error as (
  select order_id, error_type
  from (
    select order_id, error_type,
           row_number() over(partition by order_id order by created_at desc) as rn
    from payment_funnel_events
    where event_type = 'payment_error--shown'
  ) s
  where rn = 1
)

2. CTE payment_methods — max(created_at) без группировки по payment_method

Комментарий: «все использованные методы оплаты и время последнего использования» — нужно «для каждого метода» время последнего использования, поэтому группировать надо по (order_id, payment_method):

payment_methods as (
  select order_id, payment_method, max(created_at) as last_changed
  from payment_funnel_events
  where event_type = 'payment_method--selected'
  group by order_id, payment_method
)

В оригинале group by был только в подзапросе pm, а здесь забыт — SQL упадёт или вернёт некорректный агрегат (зависит от диалекта).

3. Подзапрос pe — count(... else 0 end) считает нули как валидные

count(case when event_type = 'page--shown' then created_at else null end) as 'payment_page_shown',
count(case when event_type = 'page--submit' then created_at else 0 end) as 'payment_page_submit'

COUNT(x) считает non-null. Поэтому else null — правильно (даёт «сколько показов»), а else 0 даёт «сколько строк всего», т. е. всегда сумму всех событий в payment_funnel_events. Это баг: оба значения должны быть else null или лучше использовать SUM(case when ... then 1 else 0 end):

sum(case when event_type = 'page--shown' then 1 else 0 end) as payment_page_shown,
sum(case when event_type = 'page--submit' then 1 else 0 end) as payment_page_submit

4. 'payment_page_shown' — алиас в кавычках

В большинстве диалектов SQL 'строка' — это строковый литерал, не имя столбца. Алиасы — без кавычек или в backtick-ах. Иначе колонка будет с подозрительным именем.

5. SELECT — два раза payment_page_submit

coalesce(payment_page_submit, 0) payment_page_shown,
coalesce(payment_page_submit, 0) payment_page_submit,

Первая строка должна быть coalesce(payment_page_shown, 0) payment_page_shown, — опечатка.

6. inner join last_error теряет заказы без ошибок

Комментарий к last_error: «ошибки могут случаться не во всех заказах». Значит, нужно LEFT JOIN, иначе мы уберём заказы без ошибок (а нам нужно показать error_type как NULL для них).

left join last_error le on od.order_id = le.order_id

7. inner join в pe — теряются заказы без событий воронки

Заказ может быть создан, но пользователь не дошёл до страницы оплаты — событий нет. С inner join такие заказы пропадут. Должен быть LEFT JOIN:

left join (...) pe on od.order_id = pe.order_id

8. Подзапрос pe фильтрует created_at > '10-01-2024' — двусмысленный формат и пере-фильтрация

'10-01-2024' — это 10 января или 1 октября? В большинстве СУБД нужен ISO '2024-10-01'. Если в основном WHERE стоит od.created_at > '01-01-2024', то фильтр в подзапросе должен быть синхронизирован — иначе мы потеряем события до октября для заказов из января-сентября.

Лучше — либо одинаковая граница, либо убрать из подзапроса (фильтрация в основном WHERE).

9. Двойной join payment_methods (через pm и pm_max) — overengineered

Цель: получить последний выбранный способ оплаты на момент попытки. Это можно сделать одним row_number() в payment_methods:

payment_methods as (
  select order_id, payment_method, max(created_at) as last_changed,
         row_number() over(partition by order_id order by max(created_at) desc) as rn
  from payment_funnel_events
  where event_type = 'payment_method--selected'
  group by order_id, payment_method
)
-- ...
left join payment_methods pm_max
  on od.order_id = pm_max.order_id and pm_max.rn = 1

10. Условие od.created_at > '01-01-2024' строгое — пропустит ровно «01-01-2024»

Если хотим включить — >=. Зависит от продуктовой логики.

Финальный запрос

with last_error as (
  -- ПОСЛЕДНЯЯ ошибка по заказу
  select order_id, error_type
  from (
    select order_id, error_type,
           row_number() over(partition by order_id order by created_at desc) as rn
    from payment_funnel_events
    where event_type = 'payment_error--shown'
  ) s
  where rn = 1
),
 
payment_methods as (
  -- Все методы по заказу с временем последнего использования
  select order_id, payment_method, max(created_at) as last_changed
  from payment_funnel_events
  where event_type = 'payment_method--selected'
  group by order_id, payment_method
),
 
last_payment_method as (
  -- Последний выбранный метод
  select order_id, payment_method
  from (
    select order_id, payment_method,
           row_number() over(partition by order_id order by last_changed desc) as rn
    from payment_methods
  ) s
  where rn = 1
),
 
page_events as (
  -- Показы и сабмиты страницы оплаты на заказ
  select order_id,
    sum(case when event_type = 'page--shown' then 1 else 0 end) as payment_page_shown,
    sum(case when event_type = 'page--submit' then 1 else 0 end) as payment_page_submit
  from payment_funnel_events
  where created_at >= '2024-01-01'
  group by order_id
),
 
method_counts as (
  -- Сколько раз выбирали каждый метод
  select order_id,
    sum(case when payment_method = 'bank_card' then 1 else 0 end) as selected_card_payment,
    sum(case when payment_method = 'sbp' then 1 else 0 end) as selected_sbp_payment
  from payment_methods
  group by order_id
)
 
select od.order_id,
       od.payment_started,
       od.payment_finished,
       cast(round((unix_timestamp(od.payment_finished) - unix_timestamp(od.payment_started)) / 60, 0) as int) as payment_dur,
       coalesce(pe.payment_page_shown, 0) as payment_page_shown,
       coalesce(pe.payment_page_submit, 0) as payment_page_submit,
       coalesce(mc.selected_card_payment, 0) as selected_card_payment,
       coalesce(mc.selected_sbp_payment, 0) as selected_sbp_payment,
       coalesce(lpm.payment_method, 'bank_card') as last_payment_method,
       le.error_type,
       od.pdate
from orders od
left join page_events pe on od.order_id = pe.order_id
left join method_counts mc on od.order_id = mc.order_id
left join last_payment_method lpm on od.order_id = lpm.order_id
left join last_error le on od.order_id = le.order_id
where od.created_at >= '2024-01-01';

Подводные камни

  1. «Последняя» vs «первая» в row_number. Самая частая ошибка — asc + rn=1 ≠ «последняя». Сверять order by и desc/asc.
  2. COUNT(x) vs COUNT(*). COUNT(x) пропускает NULL; COUNT(*) считает все строки. Поэтому конструкция count(case when ... then ... else 0 end) никогда не даёт «количество соответствующих строк» — даёт «количество всех строк», потому что 0 не NULL. Используйте SUM(case when ... then 1 else 0 end).
  3. INNER JOIN теряет строки. Если правая таблица не гарантирует строку для каждого ключа — LEFT JOIN.
  4. Алиасы в кавычках — это в стандарте "alias", в MySQL — backticks. Одинарные кавычки — это литерал.
  5. Группировка MAX(created_at) без group by по payment_method сольёт всё к одной строке на заказ.
  6. Формат даты MM-DD-YYYY или DD-MM-YYYY? ISO YYYY-MM-DD — единственный безопасный.
  7. Двойной join к одной таблице через промежуточные ключи (pm.max_last_changed = pm_max.last_changed) — это дорого и хрупко. Замените row_number.
  8. unix_timestamp vs timestamp_diff. В разных диалектах функции называются по-разному. В Hive/Spark unix_timestamp ОК, в Postgres — extract(epoch from ...).

Эталонный ответ

Перечисление 9 ключевых багов:

  1. last_error — desc вместо asc.
  2. payment_methods — добавить group by order_id, payment_method.
  3. count(... else 0 end) → sum(... then 1 else 0 end).
  4. Опечатка payment_page_shown / payment_page_submit.
  5. inner join → left join (для last_error, pe).
  6. '10-01-2024' → '2024-01-01'.
  7. Двойной join payment_methods → row_number.
  8. Алиасы в одинарных кавычках.
  9. Дублирующая фильтрация по created_at.

Плюс рефакторинг с CTE-ями last_error / payment_methods / last_payment_method / page_events / method_counts.

Хочешь увидеть разбор?

Зарегистрируйся бесплатно — откроется развёрнутое решение этой задачи и ещё 4 на выбор.

Зарегистрироваться и увидеть разбор
Уже есть аккаунт? Войти