Условие

В KaspiTravel (Авиа) даны три таблицы в BigQuery:

pa_hiring_task(date, timestamp, u_id, event, d_cty_name, d_ctry_name, a_ctry_name, a_cty_name, ...) — события воронки от поиска до оплаты с 03.07–14.07.2022.
pa_hiring_task_experiment_data(u_id, event, fork, date) — A/B-тест с веткой control / last-seats.
has_avia_transactions(u_id) — пользователи, которые когда-либо покупали авиабилеты.

События воронки: travel_search_form → travel_search_in_progress → travel_search_results → travel_flight_details → travel_tariff_confirmation → travel_select_passenger → travel_payment_method → (travel_card_payment_approved | travel_credit_approved).

Задания:

Сколько пользователей хотя бы раз купили билет в кредит за всё время.
Конверсия из просмотра результатов в покупку в выходные на международные направления, в разрезе новых/повторных. Международный = страна отправления или прилёта ≠ Казахстан. Только пользователи, попавшие на шаг 1 (results), учитываются на шаге 2 (purchase).
Конверсия из просмотра формы поиска в просмотр результатов поиска по дням 03.07–14.07. Оба шага в один день.

Решение

Задание 1

SELECT COUNT(DISTINCT u_id) AS n_credit_buyers
FROM `kaspi-mobile.hiring_pa_task.pa_hiring_task`
WHERE event = 'travel_credit_approved';

Задание 2 — конверсия в выходные на международные, по сегментам

WITH international AS (
  SELECT *,
         CASE WHEN d_ctry_name != 'Казахстан' OR a_ctry_name != 'Казахстан'
              THEN 1 ELSE 0 END AS is_intl
  FROM `kaspi-mobile.hiring_pa_task.pa_hiring_task`
  WHERE EXTRACT(DAYOFWEEK FROM date) IN (1, 7)        -- BQ: 1=Sun, 7=Sat
),
intl AS (
  SELECT u_id, event
  FROM international WHERE is_intl = 1
),
results_users AS (
  SELECT DISTINCT u_id FROM intl WHERE event = 'travel_search_results'
),
purchase_users AS (
  SELECT DISTINCT u_id FROM intl
  WHERE event IN ('travel_card_payment_approved', 'travel_credit_approved')
),
funnel AS (
  SELECT r.u_id,
         CASE WHEN p.u_id IS NOT NULL THEN 1 ELSE 0 END AS purchased
  FROM results_users r
  LEFT JOIN purchase_users p USING (u_id)
),
segmented AS (
  SELECT f.*,
         CASE WHEN h.u_id IS NOT NULL THEN 'repeat' ELSE 'new' END AS seg
  FROM funnel f
  LEFT JOIN `kaspi-mobile.hiring_pa_task.has_avia_transactions` h USING (u_id)
)
SELECT seg,
       COUNT(*)                                   AS users_at_results,
       SUM(purchased)                             AS users_purchased,
       ROUND(SUM(purchased) * 100.0 / COUNT(*), 1) AS cr_pct
FROM segmented
GROUP BY seg;

Задание 3 — CR форма → результаты, поднево, оба шага в один день

WITH days AS (
  SELECT date, u_id, event
  FROM `kaspi-mobile.hiring_pa_task.pa_hiring_task`
  WHERE date BETWEEN DATE '2022-07-03' AND DATE '2022-07-14'
),
form_users AS (
  SELECT DISTINCT date, u_id FROM days WHERE event = 'travel_search_form'
),
result_users AS (
  SELECT DISTINCT date, u_id FROM days WHERE event = 'travel_search_results'
)
SELECT f.date,
       COUNT(DISTINCT f.u_id)                                     AS form_users,
       COUNT(DISTINCT r.u_id)                                     AS result_users,
       ROUND(COUNT(DISTINCT r.u_id) * 100.0 /
             NULLIF(COUNT(DISTINCT f.u_id), 0), 1)                AS cr_pct
FROM form_users f
LEFT JOIN result_users r USING (date, u_id)
GROUP BY f.date
ORDER BY f.date;

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

DAYOFWEEK в BigQuery: EXTRACT(DAYOFWEEK FROM date) — 1=Sunday, 7=Saturday. Не путайте с PostgreSQL (0=Sunday).
«Хотя бы раз»: COUNT(DISTINCT u_id), иначе посчитаете повторные сессии.
Международный = OR не AND: «страна отправления или прилёта». Если используете AND — посчитаете только перелёты между двумя зарубежными странами.
«В один день»: фильтр по дате должен совпадать на обоих шагах. Иначе пользователь, открывший форму вчера и купивший сегодня, будет некорректно «зачислен» на сегодняшнюю воронку.
travel_search_in_progress не учитываем — это технический экран ожидания.
Round до десятых: ROUND(... , 1). ROUND «до десятых» = до 1 знака после запятой.

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

(1) COUNT(DISTINCT u_id) WHERE event='travel_credit_approved'. (2) Воронка results → purchase в выходные на международных, segmented по таблице has_avia_transactions. (3) Поднево form → results с фильтром «оба события в один день».

Kaspi.kz — конверсия в KaspiTravel: воронка по сегментам и BigQuery