the_best_of_baseball_awards.sql

the_best_of_baseball_awards.sql

-- Heaviest Hitters - This award goes to the team with the highest average weight of its batters on a given year
SELECT 
	ROUND(AVG(weight), 2) AS "Average Weight", 
	teams.name AS "Team Name",
	batting.yearid AS "Year"
FROM people
JOIN batting
	ON people.playerid = batting.playerid
JOIN teams
	ON  batting.team_id = teams.id
GROUP BY 
	teams.name, 
  	batting.yearid
ORDER BY 1 DESC;
-- Answer = Chicago White Sox, 2009, 221.33 average weight

-- Shortest Sluggers - This award goes to the team with the smallest average height of its batters on a given year
SELECT 
	ROUND(AVG(height), 2) AS "Average Height", 
  	teams.name AS "Team Name",
  	batting.yearid AS "Year"
FROM people
JOIN batting
	ON people.playerid = batting.playerid
JOIN teams
	ON  batting.team_id = teams.id
GROUP BY 
	teams.name, 
  	batting.yearid
ORDER BY 1 ASC;
-- Answer = Middletown Mansfields, 1872, 66.57 average height

-- Biggest Spenders - This award goes to the team with the largest total salary of all players in a given year
SELECT 
	teams.name AS "Team Name", 
  	SUM(salaries.salary) AS "Total Salary", 
  	salaries.yearid AS "Year"
FROM salaries
JOIN teams
	ON salaries.team_id = teams.id
GROUP BY 
	teams.name, 
  	salaries.teamid, 
  	salaries.yearid
ORDER BY 2 DESC;
-- Answer = New York Yankees, 2013, $231,978,886

-- Most Bang For Their Buck in 2010 - This award goes to the team that had the smallest "cost per win" in 2010
SELECT 
	teams.name AS "Team Name", 
  	ROUND((SUM(salaries.salary) / teams.w)) AS "Cost Per Win", 
  	salaries.yearid AS "Year"
FROM salaries
JOIN teams
	ON salaries.team_id = teams.id
WHERE salaries.yearid = 2010
GROUP BY 
	teams.name, 
  	salaries.yearid, 
  	teams.w
ORDER BY 2 ASC;
-- Answer = San Diego Padres, 90 team wins in 2010, $419 992 cost per win

-- Priciest Starter - This award goes to the pitcher who, in a given year, cost the most money per game in which they were the starting pitcher. Note that many pitchers only started a single game, so to be eligible for this award, you had to start at least 10 games.
SELECT 
	people.namegiven AS "Fullname", 
	ROUND(SUM(salary) / SUM(pitching.gs)) AS "Cost Per Game", 
  	salaries.yearid AS "Year",
  	pitching.gs AS "Pitching Games Started"
FROM salaries
JOIN pitching
	ON salaries.teamid = pitching.teamid
	AND salaries.yearid = pitching.yearid
	AND salaries.playerid = pitching.playerid
JOIN people
	ON salaries.playerid = people.playerid
WHERE pitching.gs > 10
GROUP BY 
	people.namegiven, 
	salaries.salary, 
  	salaries.yearid, 
  	pitching.gs
ORDER BY 2 DESC;
-- Answer = Clifton Phifer, 2014, 13 games started, $1 923 077 per game