Босс П1. Экономика Пограничья

Ставка простая: агентство держится на четырёх клиентах, и Бран был первым. Его договор теперь зависит от одной таблицы на слайде.

Это первый босс курса. Новых конструкций здесь почти нет: всё, что нужно, ты прошёл в блоках B и C. Новое в другом. Раньше каждый урок давал одну задачу и одну болячку данных. Теперь болячки приходят все сразу, а ответ нужен не в виде таблицы, а в виде вывода, который Бран отнесёт на совет.

Босс проходится в три фазы, как и все следующие. Разведка: понять, что с данными не так. Бой: посчитать метрики. Трофей: сформулировать вывод.

Фаза 1. Разведка

Опытный аналитик не начинает с sum(amount_rub). Сначала он смотрит, что лежит в таблице и чему нельзя верить. За прошлые уроки ты нашёл три болячки в purchases:

• тестовые аккаунты из урока про строки: почта на @studio.dev, покупки настоящие по форме, но денег студии не приносят;
• эхо вебхуков из урока про дубли: лишние строки с тем же receipt_id;
• возвраты: строки со status = 'refunded'.

Оценим масштаб одним запросом, условными счётчиками из урока 19:

SELECT count(*) AS total_rows,
       count(*) FILTER (
         WHERE player_id IN (SELECT id FROM players WHERE email LIKE '%@studio.dev')
       ) AS test_rows,
       count(*) - count(DISTINCT receipt_id)      AS duplicate_rows,
       count(*) FILTER (WHERE status = 'refunded') AS refunded_rows
FROM purchases;

 total_rows | test_rows | duplicate_rows | refunded_rows
------------+-----------+----------------+---------------
       1250 |        54 |             28 |            33

Из 1250 строк под подозрением больше сотни. Тестировщики сделали 54 покупки на 35 465 рублей, возвраты забрали ещё 21 363.

Договоримся об определении, которым будем пользоваться во всём бою. Чистая покупка это строка, для которой выполнены все три условия:

WHERE status = 'paid'
  AND id IN (SELECT min(id) FROM purchases GROUP BY receipt_id)
  AND player_id NOT IN (SELECT id FROM players WHERE email LIKE '%@studio.dev')

Первое условие убирает возвраты, второе эхо вебхуков, третье тестировщиков. NOT IN работает как IN наоборот: оставляет строки, чей player_id в список тестовых не входит. Эти три строки ты будешь копировать в каждый запрос боя. Неудобно, зато честно, а в блоке D узнаешь, как написать их один раз.