Best of Baseball - Heaviest Hitter & Shortest Slugger & Biggest Spenders & Most Bang For Their Buck

Projects Off-platform Practice Projects
1

Hey folks, so I’ve been struggling with this off-platform project for a few days, in part because nobody has the same code to find the answers. Which is cool. More than one way to skin the code. However, all the solutions to the “Heaviest Hitter” award that others have posted (that I’ve seen) include a “WITH” clause, but I think it doesn’t need it. Obviously, I could be wrong, so here it is:

SELECT ROUND(AVG(weight), 4) AS heaviest_hitters,
	batting.yearid AS year,
  	teams.name AS team
FROM people
JOIN batting
	ON people.playerid = batting.playerid
JOIN teams
  ON batting.teamid = teams.teamid
 WHERE batting.teamid = teams.teamid AND batting.yearid = teams.yearid
GROUP BY 2, 3
ORDER BY 1 DESC
LIMIT 1;

I saw a solution using MAX, but a LIMIT clause set to 1 does the same job.

Edit: Here’s the very similar solution to the “Shortest Slugger” task:

SELECT ROUND(AVG(height), 3) AS shortest_sluggers
	batting.yearid AS year,
  	teams.name AS team
FROM people
JOIN batting
	ON people.playerid = batting.playerid
JOIN teams
  ON batting.teamid = teams.teamid
 WHERE batting.teamid = teams.teamid AND batting.yearid = teams.yearid
GROUP BY 2, 3
ORDER BY 1 ASC
LIMIT 1;

Edit: Starting to get on a bit of a roll. It’s great way to learn by getting it wrong as many times as I have… Here’s the Biggest Spenders solution I came up with

SELECT teams.name AS biggest_spenders, -- (gets the team name instead of the team's 3-letter code)
	salaries.yearid AS year,
  	SUM(salary) AS total_salary
FROM salaries
JOIN teams
	ON salaries.teamid = teams.teamid
WHERE salaries.yearid = teams.yearid -- (without this it sums multiple years)
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 1;

Edit: Here’s the Most Bang For Their Buck (Moneyball) award. Gotta say I had to double check the total team salary was correct as I couldn’t believe how little they spent for that number of wins!

SELECT teams.name AS most_bang_for_their_buck,
	teams.w AS wins,
  	SUM(salary) AS team_salary,
    	ROUND((SUM(salary) / teams.w)) AS cost_per_win
 FROM salaries
 JOIN teams
 	ON salaries.teamid = teams.teamid
WHERE salaries.yearid = 2010 AND teams.yearid = 2010
GROUP BY 1, 2
ORDER BY 4 ASC
LIMIT 1;
2

Oops… looks like I can’t edit the first post anymore.

Here’s what I came up with for the Priciest Starter award.

SELECT CONCAT(namefirst, ' ', namelast) AS priciest_starter, -- CONCAT joins the first & last name columns together
	gs AS games_started,
  	ROUND((SUM(salaries.salary) / gs)) AS cost_per_game,
      	pitching.yearid AS year
FROM pitching
JOIN salaries
	ON pitching.playerid = salaries.playerid
JOIN people
	ON pitching.playerid = people.playerid
WHERE gs >=10 AND pitching.yearid = salaries.yearid -- Min 10 games played AND in the same year
GROUP BY priciest_starter, games_started, year
ORDER BY cost_per_game DESC
LIMIT 1;
3

For the “Create Your Own Award” task, I went with the player who has been admitted to the hall of fame who has the most career fielding errors.
It’s called the “Hall Of Fame Error?” award

SELECT CONCAT(namefirst, ' ', namelast) AS "hall_of_fame_error?",
	halloffame.yearid AS year_inducted,
		SUM(fielding.e) AS career_fielding_errors
FROM fielding
JOIN halloffame
	ON fielding.playerid = halloffame.playerid
JOIN people
	ON fielding.playerid = people.playerid
WHERE halloffame.inducted = 'Y'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 1;

Hint: the winner made over 1,000 career fielding errors!