wide_receiver_code

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

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

-- filtering out and deleting wide receivers with no targets 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
	wide_receiver
WHERE
	season = 2023
GROUP BY 
	player
HAVING 
	SUM(targets) = 0
	AND SUM(carries) = 0;

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

-- running the query below returns 468 players which is 128 less than the  we started with	
SELECT 
 	COUNT(DISTINCT player)
FROM
	wide_receiver;
	
-- inserting a new field called position
-- this field will help filter data once we model the database
ALTER TABLE wide_receiver
ADD COLUMN	position varchar(3);
-- Assigning the position abbreviation to the new field
UPDATE running_back
SET position = 'WR';
	
-- trimming all whitespaces from text fields
SELECT
	trim(player),
	trim(team_code),
	trim(position)
FROM 
	wide_receiver;
	
-- 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
	wide_receiver
GROUP BY
	player
ORDER BY
	"2021" DESC,
	player;
	
-- checking DeVonta Smith who had over 18 games in 2021
SELECT *
FROM
	wide_receiver
WHERE
	player = 'DeVonta Smith'
	AND season = 2021
ORDER BY
	week;
-- this shows dupicate values for week 13 & 14
-- fantasydata.com shows he had a bye in week 14
-- I will delete the week 14 record 
DELETE FROM 
	wide_receiver
WHERE 
	player = 'DeVonta Smith'
	AND season = 2021
    AND week = 14;

-- checking Jalen Reagor who had over 18 games in 2021
SELECT *
FROM
	wide_receiver
WHERE
	player = 'Jalen Reagor'
	AND season = 2021
ORDER BY
	week;
-- this shows dupicate values for week 13 & 14
-- fantasydata.com shows he had a bye in week 14
-- I will delete the week 14 record 
DELETE FROM 
	wide_receiver
WHERE 
	player = 'Jalen Reagor'
	AND season = 2021
    AND week = 14;
	
-- checking Kendrick Bourne who had over 18 games in 2021
SELECT *
FROM
	wide_receiver
WHERE
	player = 'Kendrick Bourne'
	AND season = 2021
ORDER BY
	week;
	
-- this shows dupicate values for week 13 & 14
-- fantasydata.com shows he had a bye in week 14
-- I will delete the week 14 record 
DELETE FROM 
	wide_receiver
WHERE 
	player = 'Kendrick Bourne'
	AND season = 2021
    AND week = 14;
	
-- checking Michael Pittman Jr who had over 18 games in 2021
SELECT *
FROM
	wide_receiver
WHERE
	player = 'Michael Pittman Jr.'
	AND season = 2021
ORDER BY
	week;
	
-- this shows dupicate values for week 13 & 14
-- fantasydata.com shows he had a bye in week 14
-- I will delete the week 14 record 
DELETE FROM 
	wide_receiver
WHERE 
	player = 'Michael Pittman Jr.'
	AND season = 2021
    AND week = 14;
	
-- checking Quez Watkins who had over 18 games in 2021
SELECT *
FROM
	wide_receiver
WHERE
	player = 'Quez Watkins'
	AND season = 2021
ORDER BY
	week;
	
-- this shows dupicate values for week 13 & 14
-- fantasydata.com shows he had a bye in week 14
-- I will delete the week 14 record 
DELETE FROM 
	wide_receiver
WHERE 
	player = 'Quez Watkins'
	AND season = 2021
    AND week = 14;
	trim(player),
	trim(team_code),
	trim(position)
FROM 
	wide_receiver;