-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathkicker_code
115 lines (103 loc) · 2.81 KB
/
kicker_code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- inserting data into kicker from csv
COPY kicker
FROM
'C:\Users\bhamm\OneDrive\Documents\FFdb_raw\mySQL\K.csv'
DELIMITER ','
CSV HEADER;
-- counting week, player (should have 104), games, and season rows
SELECT
COUNT(DISTINCT week),
COUNT(DISTINCT player),
COUNT(DISTINCT team_code),
COUNT(DISTINCT games),
COUNT(DISTINCT season)
FROM
kicker;
ALTER TABLE kicker
ADD COLUMN position varchar(3);
-- Assigning the position abbreviation to the new field
UPDATE kicker
SET position = 'K';
SELECT *
FROM kicker;
-- trimming all whitespaces from text fields
SELECT
trim(player),
trim(team_code),
trim(position)
FROM
kicker;
-- adding column under-forty which will contain values from the sum of columns
ALTER TABLE kicker
ADD COLUMN under_forty INTEGER;
-- assigning the result from adding 3 columns into under_forty
UPDATE kicker
SET under_forty = under_twenty_yds + twenty_yd_range + thirty_yd_range;
-- deleting unnecessary columns under_twenty_yds,twenty_yd_range, and thirty_yd_range
-- when calculating fantasy points these columns all provde the same points
-- they are not needed with the new under_fourty field
ALTER TABLE kicker
DROP COLUMN under_twenty_yds,
DROP COLUMN twenty_yd_range,
DROP COLUMN thirty_yd_range;
-- filtering out and deleting kickers with no field goal or extra point attempts 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
kicker
WHERE
season = 2023
GROUP BY
player
HAVING
SUM(fg_att) = 0
AND SUM(xpa) = 0;
-- the results of the above query shows 20 players that needed dropped
-- dropping players
DELETE FROM kicker
WHERE player IN (
SELECT
player
FROM
kicker
WHERE
season = 2023
GROUP BY
player
HAVING
SUM(fg_att) = 0
AND SUM(xpa) = 0);
-- running the query below returns 84 players which is 20 less than the 104 we started with
SELECT
COUNT(DISTINCT player)
FROM
kicker;
-- checking the table for any errors
SELECT *
FROM
kicker
ORDER BY
season DESC,
week,
player;
-- 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
kicker
GROUP BY
player
ORDER BY
"2023" DESC,
player;