Test_2

--в данных нет информации, в какой команде какой игрок играет
--будем считать, что, играя за свою команду, игрок сделал максимальное количество питчей
WITH t1 AS (
    SELECT pitcherId, team
    FROM
        (SELECT DISTINCT pitcherId, team, cc, MAX(cc) OVER (PARTITION BY pitcherId) ccc
        FROM(
            --считаем, сколько игроки сделали питчей
            --для выездных матчей
            SELECT DISTINCT pitcherId, awayTeamName AS team, COUNT(awayTeamName) cc
            FROM bigquery-public-data.baseball.games_wide
            WHERE pitcherId != ''
            --не будем считать наицональную лигу командой
            AND awayTeamName != 'National League'
            GROUP BY pitcherId,team
            UNION ALL
            --для домашних матчей
            SELECT DISTINCT pitcherId, homeTeamName AS team, COUNT(awayTeamName) cc
            FROM bigquery-public-data.baseball.games_wide
            WHERE pitcherId != ''
            AND awayTeamName != 'American League'
            --не будем считать американскую лигу командой
            GROUP BY pitcherId,team
            )
        )
    WHERE cc=ccc
    --находим максимум питчей, которые сделал каждый игрок во время игр
    --исходя из этого, определяем команду каждого игрока
    ORDER BY pitcherId
),
t2 AS
--находим максимальную скорость питча для каждого питчера
    (SELECT pitcherId,pitcherFirstName, pitcherLastName, MAX(maxspeed) ms
    FROM
        (SELECT pitcherId,pitcherFirstName, pitcherLastName, MAX(pitchSpeed) maxspeed
        FROM bigquery-public-data.baseball.games_wide
        --для регулярных игр сезона
        WHERE pitcherId != ''
        GROUP BY pitcherId,pitcherFirstName, pitcherLastName
        UNION ALL
        SELECT pitcherId,pitcherFirstName, pitcherLastName, MAX(pitchSpeed) maxspeed
        FROM bigquery-public-data.baseball.games_post_wide
        --для дополнительных игр сезона
        WHERE pitcherId != ''
        GROUP BY pitcherId,pitcherFirstName, pitcherLastName
        )
    GROUP BY pitcherId,pitcherFirstName, pitcherLastName),
t3 AS (
    --соединием информацию о скорости питча и команде для игроков
    SELECT t2.pitcherFirstName, t2.pitcherLastName, t1.team, t2.ms, MAX(t2.ms) OVER(PARTITION BY t1.team) mms
    FROM t1
    JOIN t2 ON t1.pitcherId = t2.pitcherId
    )
--находим скорость максимального питча для каждой команды, добавляем имя игрока
SELECT pitcherFirstName, pitcherLastName, team, mms
FROM t3
WHERE ms=mms