Тестовое задание
на продуктового аналитика
Aviasales

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'