Проект: итоги сезона для Брана

Бран · продюсер «Хроник Пограничья»

Сезон 1.9 закрыт, 14 мая выходит 2.0. Инвесторы просят итоговый отчёт: сколько заработали по месяцам и платформам, что покупали, откуда игроки идут в магазин. И отдельно: окупается ли реферальная программа? Маркетинг говорит, что друзья друзей приносят кучу денег.

Это проект блока «Продвинутый SQL». Новых конструкций нет: всё, что нужно, ты прошёл в последних шести уроках. ROLLUP даст подытоги, JSON в событиях даст источники входа в магазин, LATERAL даст топ офферов по месяцам, а рекурсивная CTE пройдёт по цепочкам приглашений. Все запросы оформлены по стайлгайду: ключевые слова заглавными, по столбцу на строку, у каждой CTE одна задача и имя по смыслу.

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

Сначала договоримся о границах. Сезон это всё до релиза 2.0, created_at < '2026-05-14' по UTC. Данные начинаются 5 января, так что в сезоне четыре полных месяца и 13 дней мая.

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

Что лежит в props

У событий есть столбец props в формате jsonb, и описания трекинга к нему никто не написал. Первое желание: открыть пару событий и посмотреть глазами. Но редкий ключ на паре событий не попадётся. Разворачиваем ключи всех событий:

SELECT
  e.name,
  k.key,
  count(*) AS events
FROM events AS e
CROSS JOIN LATERAL jsonb_object_keys(e.props) AS k (key)
GROUP BY e.name, k.key
ORDER BY e.name, events DESC;

         name         |     key      | events
----------------------+--------------+--------
 first_purchase       | app_version  |    514
 first_purchase       | currency     |    514
 first_purchase       | offer_id     |    514
 first_purchase       | price_rub    |    514
 first_quest_complete | app_version  |   2578
 install              | app_version  |   4092
 install              | device       |   4092
 install              | utm_source   |   4092
 store_open           | app_version  |   1859
 store_open           | offers_shown |   1859
 store_open           | source       |   1841
 store_open           | Source       |     18
 tutorial_complete    | app_version  |   2950
 tutorial_complete    | duration_sec |   2950
 tutorial_start       | app_version  |   3904

Почти всё ровно: app_version у всех событий, у store_open источник и список показанных офферов. Но у 18 открытий магазина ключ Source с большой буквы. Ключи в jsonb чувствительны к регистру, и props ->> 'source' для них вернёт NULL. Проверка по app_version и platform показывает: все 18 событий из версии 2.0.0 на Android, в 2.0.1 баг починили.

В наш сезон версия 2.0.0 не попадает, так что этот баг отчёт не испортит. Но если Бран попросит то же самое за полгода, источник придётся брать как coalesce(props ->> 'source', props ->> 'Source'). Запиши это в ограничения сразу, пока помнишь.

Грязь в покупках и боты

Три болячки покупок знакомы с первого босса: тестовые аккаунты @studio.dev, эхо вебхуков с тем же receipt_id и возвраты. Четвёртая новая. В таблице лидеров на верхних местах стояли подозрительные farm239x и farm619x. Проверим их по поведению: сколько квестов игрок запускает за день.

WITH runs_per_day AS (
  SELECT
    c.player_id,
    qr.started_at::date AS day,
    count(*) AS runs
  FROM quest_runs AS qr
  INNER JOIN characters AS c ON c.id = qr.character_id
  GROUP BY c.player_id, qr.started_at::date
)

SELECT
  p.nickname,
  max(rpd.runs) AS max_runs_per_day
FROM runs_per_day AS rpd
INNER JOIN players AS p ON p.id = rpd.player_id
GROUP BY p.nickname
ORDER BY max_runs_per_day DESC
LIMIT 6;

 nickname  | max_runs_per_day
-----------+------------------
 farm632x  |              220
 farm463x  |              219
 farm239x  |              218
 farm619x  |              211
 WolfFrost |                8
 Raashash  |                7

Четыре аккаунта в свой самый активный день запускают по 211-220 квестов, самый азартный живой игрок 8. Это бот-ферма. Порог «больше 50 запусков за день» ложится в огромный разрыв, им и будем ловить ботов. По нику ловить нельзя: следующая ферма назовёт аккаунты иначе.

Масштаб грязи за сезон:

 total_rows | qa_rows | duplicate_rows | refunded_rows | bots
------------+---------+----------------+---------------+------
        995 |      43 |             21 |            25 |    4

Покупок у ботов нет, деньги они не портят. Зато трое из них открывали магазин, а 15 открытий сделали тестировщики. Для среза «откуда идут в магазин» убираем и тех, и других.

Чистая покупка сезона та же, что у первого босса, плюс граница сезона:

WITH clean_purchases AS (
  SELECT *
  FROM purchases
  WHERE status = 'paid'
    AND created_at < '2026-05-14'
    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')
)

Сырые строки сезона дают 575 448 ₽, чистые 521 793 ₽ от 382 платящих. Разница 53 655 ₽, почти 10%, на совете директоров это заметно.