World of Tanks — SQL: нации, когорты по числу боёв, активные в оба дня

Условие

В базе таблица player_battles с данными о боях за день:

Префикс vehicle_name (до :) — нация техники.

Запросы:

1. Список наций, которые провели более 10 000 боёв на выбранную дату (например, 15.09.2018).
2. Распределение уникальных игроков по когортам числа боёв:
   • [0; 10), [10; 20), [20; 50), [50; 100), [100; +∞).
3. Число игроков, которые были активны в оба дня: 14.09.2018 и 15.09.2018.

Решение

Подход

Все три запроса — про корректную агрегацию с группировкой и фильтрами. Главные нюансы: правильно выделять нацию из строки, корректно считать уникальные player_id (не строки), правильно учитывать «активность» как факт хотя бы одного боя.

Реализация (PostgreSQL / Impala-совместимый синтаксис)

1. Нации с > 10 000 боёв на дату

Нация — префикс перед ::

SELECT
  SPLIT_PART(vehicle_name, ':', 1) AS nation,
  COUNT(*) AS battles
FROM player_battles
WHERE dt = DATE '2018-09-15'
GROUP BY 1
HAVING COUNT(*) > 10000
ORDER BY battles DESC;

В Impala/Hive — SPLIT_PART есть, в MySQL — SUBSTRING_INDEX(vehicle_name, ':', 1).

Тонкость: COUNT(*) vs COUNT(DISTINCT battle_id). Если в player_battles одна строка = «один игрок в одном бою», то один battle_id встречается ~30 раз (по числу участников). Что считается «боем нации»? Если каждое участие техники этой нации — отдельный бой нации, то COUNT(*) корректен. Если «бой, в котором участвовала эта нация» — нужно COUNT(DISTINCT battle_id). Уточняйте у заказчика. В типовом варианте ответа берут COUNT(*) — каждое участие как один «бой техники».

2. Когорты игроков по числу боёв

WITH player_stats AS (
  SELECT
    player_id,
    COUNT(*) AS battles
  FROM player_battles
  GROUP BY player_id
),
bucketed AS (
  SELECT
    CASE
      WHEN battles < 10               THEN '[0;10)'
      WHEN battles >= 10 AND battles < 20  THEN '[10;20)'
      WHEN battles >= 20 AND battles < 50  THEN '[20;50)'
      WHEN battles >= 50 AND battles < 100 THEN '[50;100)'
      ELSE '[100;+inf)'
    END AS cohort,
    player_id
  FROM player_stats
)
SELECT cohort, COUNT(DISTINCT player_id) AS players
FROM bucketed
GROUP BY cohort
ORDER BY MIN(CASE
  WHEN cohort = '[0;10)'    THEN 0
  WHEN cohort = '[10;20)'   THEN 1
  WHEN cohort = '[20;50)'   THEN 2
  WHEN cohort = '[50;100)'  THEN 3
  ELSE 4 END);

Сортировка по «числовой» позиции бакета — иначе [10;20) отсортируется лексикографически между [0;10) и [100;..., что неверно.

3. Активные в оба дня

Способов несколько, выберем самый читаемый:

SELECT COUNT(*) AS players_active_both_days
FROM (
  SELECT player_id
  FROM player_battles
  WHERE dt IN (DATE '2018-09-14', DATE '2018-09-15')
  GROUP BY player_id
  HAVING COUNT(DISTINCT dt) = 2
) t;

Альтернатива — INNER JOIN или INTERSECT:

SELECT COUNT(*) FROM (
  SELECT DISTINCT player_id FROM player_battles WHERE dt = DATE '2018-09-14'
  INTERSECT
  SELECT DISTINCT player_id FROM player_battles WHERE dt = DATE '2018-09-15'
) t;

HAVING COUNT(DISTINCT dt) = 2 — обычно эффективнее, чем INTERSECT (один проход).

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

Эти три запроса покрывают базовые навыки SQL:

• Парсинг строк (SPLIT_PART/SUBSTRING_INDEX).
• Условные бакеты (CASE WHEN).
• Активность за период (HAVING COUNT(DISTINCT day)).

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

1. COUNT(*) vs COUNT(DISTINCT battle_id). В таблице, где одна строка = один игрок в бою, разница важна. Спросите заказчика, что значит «бои нации».
2. Бакеты перекрываются по границам. >= 10 AND < 20 — корректное полу-открытое деление; BETWEEN 10 AND 20 включает 20 в нижний бакет — частая ошибка.
3. Сортировка бакетов по строке. Лексикографически '[100;+inf)' < '[10;20)'. Сортируйте по числовой позиции.
4. Часовой пояс / границы суток. Если dt — timestamp, а нужно «по дате» — приводите к DATE. Иначе боёв «в день 14.09» может быть меньше, чем кажется.
5. COUNT(DISTINCT player_id) против COUNT(player_id). Если один игрок имел много боёв за день, без DISTINCT — задвоит.
6. Игроки, активные «оба дня», — формально достаточно одного боя в день. Если задача требует «не меньше 5 боёв в каждый день» — уточняйте.

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

Все 3 запроса — стандартная аналитика на агрегациях:

• (1) GROUP BY nation HAVING COUNT(*) > 10000 с парсингом префикса.
• (2) CTE с COUNT(*) по игроку → CASE WHEN бакеты → COUNT(DISTINCT player_id) по бакету с правильной сортировкой.
• (3) HAVING COUNT(DISTINCT dt) = 2 для подсчёта активных в оба дня.