kivork — SQL: воронка install→trial→purchase по странам, медиана и дубликаты

Условие

Три SQL-задачи (PostgreSQL):

1. Среднее и медиана по сумме продажи. Сумма транзакции округляется до целой части. Нельзя использовать стандартные функции AVG и MEDIAN. Можно использовать только SUM и COUNT.

Схема:

• orders(id, sale_amount /* в центах */, user_id, datetime)

2. SQL для поиска задублированных транзакций (одинаковые из-за ошибки).

Схема:

• purchases(transaction_id, datetime, amount, user_id)

3. Воронка install → trial → purchase по странам. На одного юзера возможны: один trial, одна покупка. Покупка — только после конца trial.

Колонки в результате: country, installs, trials, purchases, conversion_rate_to_trial, conversion_rate_to_purchase.

Схема:

• events(transaction_id, datetime, event_type /* install / trial / purchase */, user_id, country)

Решение

Задача 1. Среднее и медиана без AVG / PERCENTILE

Среднее без AVG

AVG(x) = SUM(x) / COUNT(x). Всё разрешено.

SELECT
  ROUND(SUM(sale_amount / 100) * 1.0 / COUNT(*), 2) AS mean_sale
FROM orders;

sale_amount / 100 — переводим из центов в доллары. Округление до целой части делаем до агрегации: FLOOR(sale_amount / 100) или ROUND(sale_amount / 100). Уточните у заказчика «округление» — целочисленное деление, ROUND, FLOOR, CEIL дают разные ответы.

-- Если "до целой" = ROUND
SELECT SUM(ROUND(sale_amount / 100.0)) * 1.0 / COUNT(*) AS mean_sale
FROM orders;
 
-- Если "до целой" = FLOOR (целочисленное деление в SQL по умолчанию)
SELECT SUM(sale_amount / 100) * 1.0 / COUNT(*) AS mean_sale
FROM orders;

Медиана без MEDIAN / PERCENTILE_CONT

Идея — найти строку с ROW_NUMBER = COUNT/2 + 1 (нечётное) или среднее двух центральных (чётное).

Но условие «только SUM и COUNT» — это значит, окно ROW_NUMBER тоже нельзя. Тогда самосоединение:

-- Медиана через self-join (классика)
WITH rounded AS (
  SELECT ROUND(sale_amount / 100.0) AS amt FROM orders
)
SELECT amt AS median
FROM rounded r1
WHERE (
  SELECT COUNT(*) FROM rounded r2 WHERE r2.amt < r1.amt
) = (
  SELECT COUNT(*) FROM rounded r2 WHERE r2.amt > r1.amt
);

Это «значение, у которого равное число строк меньше и больше». Для чётного N даёт два значения — нужно усреднить:

WITH rounded AS (
  SELECT ROUND(sale_amount / 100.0) AS amt FROM orders
),
counts AS (
  SELECT amt,
    (SELECT COUNT(*) FROM rounded r2 WHERE r2.amt < r1.amt) AS less_cnt,
    (SELECT COUNT(*) FROM rounded r2 WHERE r2.amt > r1.amt) AS more_cnt
  FROM rounded r1
)
SELECT SUM(amt * 1.0) / COUNT(*) AS median  -- усреднение если их 1 или 2
FROM counts
WHERE ABS(less_cnt - more_cnt) <= 1;

Это решение работает и для нечётного, и для чётного N.

Альтернатива (менее formal): если COUNT(*) = N, то медианная позиция:

• N нечётное → (N+1)/2-я строка по ORDER BY amt.
• N чётное → среднее N/2-й и (N/2+1)-й.

Без оконных функций — это сложно на чистом SUM/COUNT. Условие задачи провокационное; разумно сообщить ограничение и использовать ROW_NUMBER или PERCENTILE_CONT(0.5), аргументируя простотой.

Задача 2. Дубликаты транзакций

«Задублированные» — это разные transaction_id, но одинаковые остальные поля (datetime, amount, user_id):

SELECT
  user_id,
  datetime,
  amount,
  COUNT(*)                                   AS dup_count,
  STRING_AGG(transaction_id::text, ', ')     AS transaction_ids
FROM purchases
GROUP BY user_id, datetime, amount
HAVING COUNT(*) > 1;

Если «дубликат» = разные transaction_id с одинаковыми (datetime, amount, user_id) — этот запрос даст пары/группы дублей.

Если «дубликат» означает повторение в коротком окне (например, < 5 секунд) — нужно self-join + временной фильтр:

SELECT a.transaction_id, b.transaction_id, a.user_id, a.amount,
       a.datetime AS dt_a, b.datetime AS dt_b
FROM purchases a
JOIN purchases b
  ON a.user_id  = b.user_id
 AND a.amount   = b.amount
 AND a.transaction_id < b.transaction_id  -- избегаем самосовпадения и дубликатов в результате
 AND b.datetime - a.datetime <= INTERVAL '5 seconds';

Уточняйте у заказчика, что именно — точное совпадение или «почти одновременные».

Задача 3. Воронка install → trial → purchase по странам

WITH per_user AS (
  SELECT
    user_id,
    country,
    BOOL_OR(event_type = 'install')  AS installed,
    BOOL_OR(event_type = 'trial')    AS trialed,
    BOOL_OR(event_type = 'purchase') AS purchased
  FROM events
  GROUP BY user_id, country
)
SELECT
  country,
  COUNT(*) FILTER (WHERE installed)  AS installs,
  COUNT(*) FILTER (WHERE trialed)    AS trials,
  COUNT(*) FILTER (WHERE purchased)  AS purchases,
  COUNT(*) FILTER (WHERE trialed)    * 1.0 /
    NULLIF(COUNT(*) FILTER (WHERE installed), 0)  AS conversion_rate_to_trial,
  COUNT(*) FILTER (WHERE purchased)  * 1.0 /
    NULLIF(COUNT(*) FILTER (WHERE trialed), 0)    AS conversion_rate_to_purchase
FROM per_user
GROUP BY country
ORDER BY installs DESC;

Тонкости:

• BOOL_OR(condition) — true, если хоть одно событие нужного типа было у юзера. PostgreSQL-специфично; в MySQL — MAX(event_type = 'install').
• COUNT(*) FILTER (WHERE ...) — postgres-специфично; альтернатива — SUM(CASE WHEN ... THEN 1 ELSE 0 END).
• Если у юзера может быть разная страна в разных событиях — country стоит брать «country первого install» через subquery.
• CR_to_trial = trials / installs, CR_to_purchase = purchases / trials (а не от installs!) — это stage-to-stage конверсия. Если бизнес хочет «from install» — нужно purchases / installs.

Если у юзера могут быть события из разных стран

WITH user_country AS (
  -- Страна первой install-операции
  SELECT user_id, country
  FROM events e1
  WHERE event_type = 'install'
    AND datetime = (SELECT MIN(datetime) FROM events e2
                    WHERE e2.user_id = e1.user_id AND e2.event_type = 'install')
),
...

Анализ / интерпретация

Воронка по странам — стандартная картина:

• Tier-1 страны (US, UK): дорогой UA, но высокая CR в paid. CR_install→purchase часто 1–3%.
• Tier-2 / 3: дешёвый UA, низкая CR. Может быть 0.1–0.5%.

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

1. «Округление до целой» — какое именно? FLOOR, ROUND, CEIL дают разные ответы. Уточните.
2. «Только SUM и COUNT» для медианы — формально требует self-join, что менее эффективно. В реальной жизни используйте PERCENTILE_CONT(0.5).
3. Дубликаты по transaction_id vs «логические дубликаты». Уточните.
4. BOOL_OR / FILTER — postgres. На других диалектах используйте MAX(CASE WHEN ...).
5. NULLIF(_, 0) — против деления на 0.
6. Конверсия от чего считается. CR_to_purchase = purchases/trials (логично) или purchases/installs (от верха воронки)? Уточняйте.
7. country юзера может меняться — VPN. Используйте country первого install.
8. Trial может быть отменён до покупки — но условие говорит «одна покупка после trial». Если trial-канселед, юзер не покупает.

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

1. Mean = SUM / COUNT. Median — через self-join «равное число строк меньше и больше». 2. GROUP BY user_id, datetime, amount HAVING COUNT(*) > 1 для точных дубликатов. 3. Воронка через CTE per_user с BOOL_OR, затем COUNT(*) FILTER по странам, CR_to_trial и CR_to_purchase с NULLIF(_, 0).