Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
WITH agg AS (
SELECT
seasonId,
majorLeagueId,
playerId,
atBats,
battingAverage,
RANK() OVER(
PARTITION BY
seasonId,
majorLeagueId
ORDER BY
battingAverage DESC
) ranking
FROM agg_batting_stats
WHERE
groupingDescription = 'MAJORLEAGUEID_SEASONID_GAMETYPE2_PLAYERID'
AND gameType2 = 'RS'
AND seasonId >= 2010
AND atbats >= 100
ORDER BY
seasonId,
majorLeagueId,
battingAverage DESC
),
f AS (
SELECT
*
FROM agg
WHERE
ranking = 1
),
s AS (
SELECT
*
FROM agg
WHERE
ranking = 2
)
SELECT
f.seasonId,
ml.majorLeague,
CONCAT(fp.firstName, ' ', fp.lastName) jugador1,
f.atBats ab1,
f.battingAverage ba1,
CONCAT(sp.firstName, ' ', sp.lastName) jugador2,
s.atBats ab2,
s.battingAverage ba2,
f.battingaverage - s.battingAverage baDifference,
ABS(f.atBats - s.atBats) abDifference
FROM f
INNER JOIN s
ON f.seasonId = s.seasonId
AND f.majorLeagueId = s.majorLeagueId
INNER JOIN players fp
ON f.playerId = fp.playerId
INNER JOIN players sp
ON s.playerId = sp.playerId
INNER JOIN major_leagues ml
ON f.majorLeagueId = ml.majorLeagueId
WHERE
f.battingAverage - s.battingAverage < 0.01
ORDER BY
ABS(f.atBats - s.atBats)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment