Условие

В SQLite-базе World of Warships есть 4 таблицы (см. описание):

arenas, arena_members, glossary_ships, catalog_items.

arena_members содержит данные по сыгранным боям: account_db_id, vehicle_type_id, damage, и т.д. Боты имеют отрицательный account_db_id.

Задание 2.1. Топ 5% игроков по суммарному урону за все бои.

Результат: | account_db_id | total_damage |

Задание 2.2. Для каждого игрока из 2.1 найдите корабль, на котором он нанёс больше всего урона. Ограничьте 10 лучшими результатами.

Результат: | account_db_id | ship_name | dealt_damage |

Решение

Подход

Задание 2.1: вычислить total_damage per игрока, затем выбрать top-5% по перцентилю.

Задание 2.2: для каждого игрока — ROW_NUMBER OVER (PARTITION BY player ORDER BY damage_per_ship DESC) = 1.

Реализация

Задание 2.1. Топ-5%

WITH player_damage AS (
  SELECT
    account_db_id,
    SUM(damage) AS total_damage
  FROM arena_members
  WHERE account_db_id > 0  -- исключаем ботов
  GROUP BY account_db_id
),
ranked AS (
  SELECT
    account_db_id,
    total_damage,
    PERCENT_RANK() OVER (ORDER BY total_damage) AS pr
  FROM player_damage
)
SELECT
  account_db_id,
  total_damage
FROM ranked
WHERE pr >= 0.95
ORDER BY total_damage DESC;

PERCENT_RANK() — стандартная оконная функция; даёт долю строк со значением меньше текущего. Топ 5% — это pr >= 0.95.

Альтернатива — через NTILE(20):

NTILE(20) OVER (ORDER BY total_damage)

20 бакетов по 5%; топ — ntile = 20. Чуть менее точный (могут быть пограничные эффекты с дубликатами), но иногда проще читается.

Задание 2.2. Лучший корабль каждого топ-игрока (с лимитом 10)

WITH player_damage AS (
  SELECT
    account_db_id,
    SUM(damage) AS total_damage
  FROM arena_members
  WHERE account_db_id > 0
  GROUP BY account_db_id
),
ranked AS (
  SELECT
    account_db_id,
    total_damage,
    PERCENT_RANK() OVER (ORDER BY total_damage) AS pr
  FROM player_damage
),
top5pct AS (
  SELECT account_db_id, total_damage
  FROM ranked
  WHERE pr >= 0.95
),
ship_damage AS (
  SELECT
    am.account_db_id,
    am.vehicle_type_id,
    SUM(am.damage) AS dealt_damage
  FROM arena_members am
  JOIN top5pct      p USING (account_db_id)
  WHERE am.account_db_id > 0
  GROUP BY am.account_db_id, am.vehicle_type_id
),
best_ship AS (
  SELECT
    account_db_id,
    vehicle_type_id,
    dealt_damage,
    ROW_NUMBER() OVER (PARTITION BY account_db_id
                       ORDER BY dealt_damage DESC) AS rn
  FROM ship_damage
)
SELECT
  bs.account_db_id,
  gs.ship_name,
  bs.dealt_damage
FROM best_ship bs
JOIN top5pct USING (account_db_id)
JOIN glossary_ships gs ON gs.id = bs.vehicle_type_id
WHERE bs.rn = 1
ORDER BY top5pct.total_damage DESC
LIMIT 10;

Ключевые моменты:

top5pct — те же 5%, что и в 2.1.
ship_damage — total_damage каждого юзера на каждом корабле.
ROW_NUMBER OVER (PARTITION BY account_db_id ORDER BY dealt_damage DESC) — даёт лучший корабль каждого юзера.
Финальный фильтр rn = 1 + LIMIT 10 (отсортировано по общему урону игрока, чтобы среди 5% выбрать топ-10 по абсолюту).

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

Запрос даёт 10 «лучших среди лучших» — игроков из топ-5% по суммарному урону, и для каждого — корабль, на котором он раскрылся максимально. Полезно для команды разработки: топ-игроки на каких кораблях.

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

Боты (account_db_id < 0) — обязательно фильтровать.
PERCENT_RANK vs NTILE. PERCENT_RANK при дубликатах даёт «честное» значение; NTILE может разбросать одинаковые значения по разным бакетам.
«Топ 5%» — какой границы? pr >= 0.95 — это «верхние 5%», т.е. от 95-го перцентиля и выше. Если задача «строго 5%», может оказаться 5.5% (из-за tie-breaker на 95-й перцентиль). Уточните у бизнеса.
Игроки с одним боем. Их total_damage = damage одного боя. С большой вероятностью они в нижних перцентилях. Но если их много, они «портят» распределение. Можно добавить HAVING COUNT(*) >= N.
SUM(damage) per ship vs MAX(damage in single battle). «Больше всего урона на корабле» обычно = суммарный по всем боям; если задача про «лучший бой» — MAX(damage).
JOIN с glossary_ships. vehicle_type_id на стороне arena_members, а в glossary_ships — поле id. Проверяйте имена ключей.
LIMIT 10 сортировка. По чему ограничивать — по dealt_damage корабля или по total_damage игрока? В моём решении — по total_damage игрока (10 «топ-игроков из топ-5%»). Уточните.
SQLite поддерживает PERCENT_RANK только с версии 3.25 (2018). На старых — нужно эмулировать через (RANK() - 1.0) / (COUNT(*) OVER () - 1).

Альтернативы

-- Без PERCENT_RANK, через CTE с количеством
WITH player_damage AS (...),
ranked AS (
  SELECT *,
         RANK() OVER (ORDER BY total_damage DESC) AS rk,
         COUNT(*) OVER ()                         AS total_players
  FROM player_damage
)
SELECT account_db_id, total_damage
FROM ranked
WHERE rk <= total_players * 0.05;

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

2.1: CTE с SUM(damage) per игрока (фильтр ботов) + PERCENT_RANK() OVER (ORDER BY total_damage) ≥ 0.95.

2.2: дополнительный CTE с SUM(damage) per (игрок, корабль), ROW_NUMBER OVER (PARTITION BY player ORDER BY damage DESC) = 1, JOIN с glossary_ships для названия. Финальный LIMIT 10.

LESTA — SQL: топ-5% игроков по урону и их лучший корабль