Best of Baseball Awards

Hi everyone,
I just finished the off-platform project for Best of Baseball Awards.

Wanted to share my code here: postgreSQL/baseball at main · pwong09/postgreSQL · GitHub

I didn’t make my own award b/c who cares about baseball?!?! But I did enjoy doing this project and gaining some firsthand experience in retrieving a large data set and building my queries to the required awards.

How did everyone else approach this project? Was anyone else annoyed with Postbird’s Group By requirement to always put in every column you SELECT’ed?

Great job! I think the first one you wrote is incomplete. I think you need to filter by year so you get the highest average weighing team per year.

My code for the first one is very messy but gets the job done:

WITH initial AS (
SELECT batting.yearid, teams.name, AVG(weight)
FROM batting
LEFT JOIN people ON batting.playerid = people.playerid
LEFT JOIN teams ON teams.teamid = batting.teamid
GROUP BY batting.yearid, teams.name
ORDER BY yearid ASC
),
highestAverage AS (
SELECT yearid, MAX(avg)
FROM initial
GROUP BY yearid
)
SELECT initial.yearid AS Year, name AS Team, avg AS AverageWeight
FROM initial
CROSS JOIN highestAverage
WHERE avg = max;

I’ve found that breaking down things into WITH s and approaching each step one at a time then refactoring it later is better. Sometimes though, I just don’t know how to refactor it lol.

@pearlwong