Skip to content

Instantly share code, notes, and snippets.

@andres-alvarado

andres-alvarado/im.sql Secret

Last active Jun 7, 2021
Embed
What would you like to do?
WITH p AS (
SELECT
gamePk,
inning,
halfInning,
COUNT(1) pitches
FROM pitches
GROUP BY
1, 2, 3
HAVING
COUNT(1) = 9
),
pl AS (
SELECT
playerId,
CONCAT(firstName, ' ', lastName) playerName
FROM players
),
ii AS (
SELECT
ab.gamePk,
ab.inning,
ab.halfInning,
ab.pitchingTeamId,
pl.playerName pitcherName,
GROUP_CONCAT(
pl2.playerName
ORDER BY
ab.atBatIndex
) batterNames
FROM atbats ab
INNER JOIN p
ON ab.gamePk = p.gamePk
AND ab.inning = p.inning
AND ab.halfInning = p.halfInning
INNER JOIN pl
ON ab.pitcherId = pl.playerId
INNER JOIN pl pl2
ON ab.batterId = pl2.playerId
GROUP BY
1, 2, 3, 4, 5
HAVING
COUNT(1) = 3
AND SUM(IF(event = 'Strikeout', 1, 0)) = 3
)
SELECT
g.seasonId,
g.majorLeague,
g.gameType2,
g.gameDate,
IF(g.homeTeamId = ii.pitchingTeamId, homeTeamName, awayTeamName) pitchingTeamName,
IF(g.homeTeamId = ii.pitchingTeamId, awayTeamName, homeTeamName) battingTeamName,
ii.inning,
ii.halfInning,
ii.pitcherName,
ii.batterNames,
CONCAT('https://www.milb.com/gameday/', g.gamePk, '#game_state=final,game_tab=play-by-play') gameDayLink
FROM games g
INNER JOIN ii
ON g.gamePk = ii.gamePk
WHERE seasonId >= 2010;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment