quarterback_code

-- inserting data into quarteback from csv
COPY quarterback
FROM
	'C:\Users\bhamm\OneDrive\Documents\FFdb_raw\mySQL\QB.csv'
DELIMITER ','
CSV HEADER;

-- counting week, player (should have 173), games, and season rows
SELECT 
	COUNT(DISTINCT week),
	COUNT(DISTINCT player),
	COUNT(DISTINCT games),
	COUNT(DISTINCT season)
FROM
	quarterback;

-- filtering out and deleting quarterbacks with no pass_att or carries in 2023
-- players without 2023 stats add no value to the database
-- first I first count the number of players being dropped before dropping them
SELECT
	player
FROM
	quarterback
WHERE
	season = 2023
GROUP BY 
	player
HAVING 
	SUM(pass_att) = 0
	AND SUM(carries) = 0;

-- the results of the above query shows 37 players that needed dropped
-- dropping players
DELETE FROM quarterback
WHERE player IN (
				SELECT
					player
				FROM
					quarterback
				WHERE
					season = 2023
				GROUP BY 
					player
				HAVING 
					SUM(pass_att) = 0
					AND SUM(carries) = 0);

-- running the query below returns 136 players which is 37 less than the 104 we started with	
SELECT 
 	COUNT(DISTINCT player)
FROM
	quarterback;

-- trimming all whitespaces from text fields
SELECT
	trim(player),
	trim(team_code),
	trim(position)
FROM 
	quarterback;
	
-- checking for duplicates by cross referencing games played per season
-- creating a pivot table using the FILTER clause to display games played per season
-- max games played per season from 2023-2021 = 17 and from 2020-2018 = 16
-- players with more games played than that suggest errors in the data
SELECT
	DISTINCT player,
	COALESCE(SUM(games) FILTER(WHERE season = 2023),0) AS "2023",
	COALESCE(SUM(games) FILTER(WHERE season = 2022),0) AS "2022",
	COALESCE(SUM(games) FILTER(WHERE season = 2021),0) AS "2021",
	COALESCE(SUM(games) FILTER(WHERE season = 2020),0) AS "2020",
	COALESCE(SUM(games) FILTER(WHERE season = 2019),0) AS "2019",
	COALESCE(SUM(games) FILTER(WHERE season = 2018),0) AS "2018"
FROM
	quarterback
GROUP BY
	player
ORDER BY
	"2023" DESC,
	player;
--  All players have the correct max total number of games