SQL — статистика партнёров и top-N по дню (BigQuery)

Условие

Компания предоставляет партнёрам платёжный сервис. Таблицы:

• transactions(transaction_id, transaction_datetime, partner_id, country_id, purchase_sum, purchase_currency, id_transaction_status) — purchase_sum в валюте платежа.
• transactions_status(id, description) — словарь статусов: 1=Создана, 2=Отменена, 3=Успешна.
• currency_kurs(id, name, kurs) — курс валюты к USD.

Задачи (на BigQuery):

1. По каждому партнёру — число транзакций, средний чек в USD, конверсия из всех транзакций в успешную покупку.
2. Полный список транзакций по топ-5 партнёрам (топ — по числу успешных транзакций).
3. Первые 10 транзакций за каждый день (по transaction_datetime).

Решение

Запрос 1. Агрегаты по партнёру

«Средний чек в USD» — средняя сумма успешной покупки в долларах. «Конверсия» — successful / total.

WITH t AS (
  SELECT
    tx.partner_id,
    tx.id_transaction_status,
    tx.purchase_sum / c.kurs AS purchase_usd,   -- 1 USD = 1, RUB = 77.51 → деление
    CASE WHEN tx.id_transaction_status = 3 THEN 1 ELSE 0 END AS is_success
  FROM `transactions` tx
  LEFT JOIN `currency_kurs` c
    ON c.id = tx.purchase_currency
)
SELECT
  partner_id,
  COUNT(*)                                       AS total_transactions,
  AVG(IF(is_success = 1, purchase_usd, NULL))    AS avg_check_usd_success,
  SAFE_DIVIDE(SUM(is_success), COUNT(*))         AS conversion_success
FROM t
GROUP BY partner_id
ORDER BY total_transactions DESC;

Замечание про «средний чек»: в задании не уточнено, считать ли средний по всем транзакциям или только по успешным. Обе версии стоит написать с пометкой. Чаще «средний чек» = средняя сумма по успешным транзакциям (попавшим в кассу).

Запрос 2. Транзакции топ-5 партнёров

WITH success_cnt AS (
  SELECT partner_id,
         COUNTIF(id_transaction_status = 3) AS success_cnt
  FROM `transactions`
  GROUP BY partner_id
),
top5 AS (
  SELECT partner_id
  FROM success_cnt
  ORDER BY success_cnt DESC
  LIMIT 5
)
SELECT tx.*
FROM `transactions` tx
JOIN top5 USING (partner_id)
ORDER BY tx.partner_id, tx.transaction_datetime;

Запрос 3. Первые 10 транзакций за день

«Первые» = ранние по времени.

SELECT
  transaction_id,
  partner_id,
  transaction_datetime,
  purchase_sum,
  purchase_currency,
  id_transaction_status
FROM `transactions`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY DATE(transaction_datetime)
  ORDER BY transaction_datetime ASC, transaction_id ASC
) <= 10
ORDER BY transaction_datetime;

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

1. Деление на курс или умножение? Если kurs — «сколько X стоит 1 USD» (как в задании: RUB = 77.51), то purchase_sum_usd = purchase_sum / kurs. Если бы было «сколько USD стоит 1 X», нужно было бы умножать. Уточнять формат.
2. «Конверсия в успешную покупку». Знаменатель — все транзакции (Создана, Отменена, Успешна) или только не-Created? Условие говорит «из всех» → знаменатель = все.
3. «Средний чек». Среднее по всем чекам или по успешным? Чаще — по успешным. Лучше явно разделить в запросе.
4. Топ-5 по «успешным транзакциям», не по выручке. Формулировка задания строгая.
5. Тies при LIMIT 5. Если 5-е и 6-е место с одинаковым количеством успешных — лишний детерминизм. Можно через DENSE_RANK() и WHERE rk <= 5.
6. Часовой пояс при DATE(transaction_datetime). В BigQuery DATETIME без TZ, а TIMESTAMP — UTC. Если важна местная дата, делать DATE(transaction_datetime, 'Europe/Moscow').
7. Tie-break «первые 10». При одинаковом времени двух транзакций нужен второй ключ (например, transaction_id), иначе порядок недетерминирован.
8. NULL в purchase_currency. LEFT JOIN currency_kurs оставит kurs = NULL → purchase_sum / NULL = NULL. Проверить, что таких строк нет, или фильтровать.

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

1. Q1: GROUP BY partner_id с COUNT(*), AVG (по успешным) после JOIN с currency_kurs (деление на курс), SUM(is_success) / COUNT(*) для конверсии.
2. Q2: подзапрос с COUNTIF(status=3) → LIMIT 5 → JOIN исходной таблицы. Для production-надёжности — DENSE_RANK <= 5 вместо LIMIT.
3. Q3: ROW_NUMBER() OVER (PARTITION BY DATE(transaction_datetime) ORDER BY transaction_datetime, transaction_id) <= 10 через QUALIFY (BigQuery позволяет).

Главные точки внимания: правильный знаменатель в конверсии, правильная семантика «среднего чека», часовой пояс в DATE(...) и tie-break при ранжировании.