Условие

Использовать открытый набор данных bigquery-public-data.baseball. Для каждой команды за все сезоны найти питчера с самой высокой скоростью броска (pitchSpeed). Вывести имя и фамилию.

Результат отсортировать по названию команды по возрастанию.

В качестве ответа — SQL-запрос и сами данные.

Решение

Шаг 1. Где находятся нужные поля

В bigquery-public-data.baseball основные таблицы:

games_post_wide, games_wide, schedules — игры.
В этих таблицах для каждой подачи есть строка с pitcherId, pitchSpeed, homeTeamName, awayTeamName, inningHalf (top — подаёт home, bottom — подаёт away).
Имена игроков: pitcherFirstName, pitcherLastName.

Шаг 2. Команда питчера в момент подачи

Питчер за матч играет за одну команду — это можно вывести из inningHalf:

CASE WHEN inningHalf = 'TOP'    THEN homeTeamName
     WHEN inningHalf = 'BOTTOM' THEN awayTeamName END AS pitcherTeam

(В TOP-подаче бьёт гость → подаёт хозяин, и наоборот.)

Шаг 3. Запрос

WITH pitches AS (
  SELECT
    pitcherId,
    pitcherFirstName,
    pitcherLastName,
    pitchSpeed,
    CASE WHEN inningHalf = 'TOP'    THEN homeTeamName
         WHEN inningHalf = 'BOTTOM' THEN awayTeamName END AS team
  FROM `bigquery-public-data.baseball.games_wide`
  WHERE pitchSpeed IS NOT NULL
),
ranked AS (
  SELECT
    team,
    pitcherFirstName,
    pitcherLastName,
    pitchSpeed,
    ROW_NUMBER() OVER (PARTITION BY team ORDER BY pitchSpeed DESC) AS rn
  FROM pitches
)
SELECT team,
       pitcherFirstName,
       pitcherLastName,
       pitchSpeed
FROM ranked
WHERE rn = 1
ORDER BY team ASC;

Шаг 4. Альтернатива через QUALIFY (BigQuery)

SELECT
  CASE WHEN inningHalf = 'TOP'    THEN homeTeamName
       WHEN inningHalf = 'BOTTOM' THEN awayTeamName END AS team,
  pitcherFirstName,
  pitcherLastName,
  pitchSpeed
FROM `bigquery-public-data.baseball.games_wide`
WHERE pitchSpeed IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY CASE WHEN inningHalf = 'TOP'    THEN homeTeamName
                    WHEN inningHalf = 'BOTTOM' THEN awayTeamName END
  ORDER BY pitchSpeed DESC
) = 1
ORDER BY team ASC;

Шаг 5. Проверка

В выводе должна быть одна строка на команду.
Если у двух питчеров команды одинаковая скорость броска — ROW_NUMBER оставит произвольно одного. Если хочется всех, замените на RANK() = 1.
Если в данных бывают NULL имена — обернуть в IFNULL или фильтровать.

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

MAX(pitchSpeed) без оконных функций. Простая агрегация даст правильную скорость, но не имя — JOIN обратно в таблицу даст лишние строки и потенциально дубликаты.
Группировка по pitcher вместо по team. Тогда получится «личный максимум каждого питчера», не «самый быстрый команды».
Пропуск условия pitchSpeed IS NOT NULL. В реальных данных есть пустые поля; они «выиграют» при ORDER BY DESC в некоторых СУБД (NULL trades large), но в BigQuery NULL идёт последним при DESC — обманывает редко, но фильтр всё равно нужен для чистоты.
Команды home/away. Без inningHalf команду питчера определить нельзя. Иногда кандидаты пишут homeTeamName всегда — это ошибка.
Сортировка по команде ASC. В условии прямо сказано — иначе тест не зачтут.
RANK vs ROW_NUMBER. При ties RANK вернёт несколько игроков для одной команды. Нужно явно решить.

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

SELECT team, pitcherFirstName, pitcherLastName, pitchSpeed
FROM (
  SELECT
    CASE WHEN inningHalf = 'TOP'    THEN homeTeamName
         WHEN inningHalf = 'BOTTOM' THEN awayTeamName END AS team,
    pitcherFirstName, pitcherLastName, pitchSpeed,
    ROW_NUMBER() OVER (
      PARTITION BY CASE WHEN inningHalf = 'TOP'    THEN homeTeamName
                        WHEN inningHalf = 'BOTTOM' THEN awayTeamName END
      ORDER BY pitchSpeed DESC
    ) AS rn
  FROM `bigquery-public-data.baseball.games_wide`
  WHERE pitchSpeed IS NOT NULL
)
WHERE rn = 1
ORDER BY team ASC;

Идея: правильно вывести команду питчера через inningHalf, выбрать максимум pitchSpeed оконной функцией ROW_NUMBER() ... = 1, отсортировать по команде.

SQL — самый быстрый питчер каждой команды (BigQuery baseball)