Босс П4. Маги имба?

Это четвёртый босс курса и первый, где заказчик пришёл не с вопросом, а с готовым ответом, да ещё с цифрой от твоего же агентства. Подтвердить проще всего: Ильва довольна, Ким прав, совет пройдёт быстро. Но если огненный шар урежут зря, маги станут слабыми, игроки уйдут, и в следующем патче Ильва спросит, кто дал ей эту цифру. Задача аналитика здесь честно проверить интуицию заказчика, в какую бы сторону ни вышел ответ. Новых конструкций почти нет: всё нужное ты прошёл в блоке F и раньше, а «Весы баланса» ждут последнего этапа.

Фазы те же, что у прошлого босса. Разведка: понять, какие матчи вообще можно сравнивать. Бой: посчитать силу фракций с поправкой на объём, разобрать короткие и длинные игры, популярность и серии, собрать весы. Трофей: вывод для Ильвы.

Договоримся о терминах. Винрейт фракции это доля побед среди строк match_players с этой фракцией. Одна строка это один игрок в одном матче.

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

Сначала посмотрим, на чём вообще играли:

WITH mirror AS (
  SELECT match_id
  FROM match_players
  GROUP BY match_id
  HAVING count(DISTINCT faction_id) = 1
)
SELECT p.version,
       count(m.id) AS matches,
       count(m.id) FILTER (WHERE m.id IN (SELECT match_id FROM mirror)) AS mirror_matches
FROM patches p
LEFT JOIN matches m ON m.patch_id = p.id
GROUP BY p.id, p.version, p.released_at
ORDER BY p.released_at;

 version | matches | mirror_matches
---------+---------+----------------
 1.2     |    3542 |            487
 1.3     |    2562 |            393
 1.3.1   |       0 |              0
 1.4     |    2974 |            449
 1.5     |    3106 |            410

Две находки. Хотфикс 1.3.1 есть в таблице патчей, но матчей на нём ноль: его откатили через три часа. Хорошо, что мы сделали LEFT JOIN, иначе патч бы молча пропал, и кто-нибудь потом искал бы его в отчёте.

Вторая находка: примерно каждый седьмой матч зеркальный, все игроки за одну фракцию. В зеркале магов один маг выигрывает, другой проигрывает, и такой матч всегда добавляет ровно 50%. О силе магов против других фракций он ничего не говорит, только тянет винрейт к половине.

Теперь проверим цифру Ильвы и посмотрим, как она меняется после каждой очистки:

WITH mirror AS (
  SELECT match_id FROM match_players GROUP BY match_id HAVING count(DISTINCT faction_id) = 1
),
mage AS (
  SELECT mp.result, m.ranked,
         mp.match_id IN (SELECT match_id FROM mirror) AS is_mirror
  FROM match_players mp
  JOIN matches m ON m.id = mp.match_id
  WHERE m.patch_id = 4 AND mp.faction_id = 2
)
SELECT count(*) AS raw_games,
       round(100.0 * count(*) FILTER (WHERE result = 'win') / count(*), 1) AS raw_pct,
       round(100.0 * count(*) FILTER (WHERE result = 'win' AND NOT ranked)
             / count(*) FILTER (WHERE NOT ranked), 1) AS unranked_pct,
       round(100.0 * count(*) FILTER (WHERE result = 'win' AND ranked)
             / count(*) FILTER (WHERE ranked), 1) AS ranked_pct,
       count(*) FILTER (WHERE ranked AND NOT is_mirror) AS clean_games,
       round(100.0 * count(*) FILTER (WHERE result = 'win' AND ranked AND NOT is_mirror)
             / count(*) FILTER (WHERE ranked AND NOT is_mirror), 1) AS clean_pct
FROM mage;

 raw_games | raw_pct | unranked_pct | ranked_pct | clean_games | clean_pct
-----------+---------+--------------+------------+-------------+-----------
      2266 |    53.3 |         59.1 |       51.1 |        1356 |      51.4

Где тут дыра

Вот запрос, которым Ким получил свою цифру:

SELECT round(100.0 * count(*) FILTER (WHERE mp.result = 'win') / count(*), 1) AS win_pct
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
WHERE m.patch_id = 4 AND mp.faction_id = 2;

 win_pct
---------
    53.3

Запрос верный, ошибки в синтаксисе нет, и число он считает честно. Дыра в вопросе: Ким сложил в одну кучу всё, что лежит в таблице. Таблица выше показывает, из чего эта куча состоит.

Ни Ильва, ни Ким не придумали свои 53,3%: в сырых данных патча 1.4 маги действительно выигрывают больше половины. Но почти весь перевес даёт неранговая игра, где маги побеждают в 59,1% случаев. В неранговых матчах подбор не ищет соперника своего уровня: опытный маг попадает на новичка, и огненный шар раннего натиска решает исход. Рейтинговый подбор сводит равных, и там у магов 51,1%, а без зеркал 51,4%.

Для баланса важна ранговая игра без зеркал. Дальше считаем только такие строки и называем их чистыми.