Spaces:
Paused
Paused
File size: 21,517 Bytes
367f592 | 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 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 | -- =============================================================================
-- frontal-lobe2 : DEFENSE POND -- the toxic pond
-- =============================================================================
-- Every other pond in this ramp measures an offensive player's own team. This
-- one measures the OPPOSING defense he is about to face, because that defense
-- is the thing that decides whether a usage-index read cashes on a RotoWire
-- line or not. A player can be exactly as trusted as the lake says and still
-- not deliver the yards, because the defense he drew that week is toxic.
--
-- Kept to exactly the 8 stat columns specified, no more:
-- 1. NFL ranking in defense (scoring defense, points allowed per game)
-- 2. Ranking in sacks
-- 3. Ranking in interceptions
-- 4. Points allowed in the 2nd half
-- 5. Team win percentage
-- 6. Who gets the sacks (individual)
-- 7. Who intercepts the ball (individual)
-- 8. Defensive rank in turnovers created
--
-- Plus THE IB SCORE on top -- see below. This is the "quarterback comfort"
-- read the user asked for: pressure up the middle collapses a QB's comfort,
-- and a QB who is comfortable produces the exact stat signature the user
-- described -- a back over 100 rushing, multiple receivers over 60, four
-- passing TDs. The evidence for the score is IN those stats: how a defense is
-- being scored on, and how often.
--
-- SOURCE: everything here already exists in this lake. No new vendor needed.
-- silver.nfl_team_week -- def_sacks, def_interceptions,
-- def_fumbles_forced, def_tds
-- silver.nfl_pfr_adv_defense_week -- def_pressures, def_times_blitzed,
-- def_completion_pct, def_yards_allowed_
-- per_tgt, def_adot, def_passer_rating_
-- allowed (the PFR-mirrored coverage
-- stats -- see docs/PFR_SOURCE_NOTE.md)
-- silver.nfl_player_defense_week -- individual sacks/INTs (#6, #7)
-- silver.nfl_schedule -- final scores, win/loss, home/away
-- nflverse pbp (already pulled for the TD log/usage pond) -- quarter-level
-- scoring, needed ONLY for #4 (2nd-half points allowed), which is not
-- published as a team-week aggregate anywhere in nflverse.
--
-- Engine: DuckDB. Depends on sql/nfl_pfr_schema.sql, sql/nfl_schedule_schema.sql.
-- =============================================================================
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;
-- =============================================================================
-- BASE TABLE: points allowed by half. The one stat in this pond not already
-- sitting in a team-week table anywhere in nflverse -- has to be built from
-- pbp scoring plays, same source as silver.nfl_td_log.
--
-- GRAIN: one row per team per game per half. A defense's 2nd-half number is
-- what should worry a bettor holding a 2nd-half prop or a team total -- a unit
-- that tightens up after halftime adjustments is a different bet than one
-- that caved for four quarters.
-- =============================================================================
CREATE TABLE IF NOT EXISTS silver.nfl_points_by_half (
season INTEGER NOT NULL,
week INTEGER NOT NULL,
game_id VARCHAR NOT NULL,
team VARCHAR NOT NULL, -- the DEFENSE (points allowed BY this team)
opponent VARCHAR NOT NULL,
half INTEGER NOT NULL, -- 1 or 2 (OT folded into 2nd half)
points_allowed INTEGER NOT NULL,
ingest_run_id VARCHAR NOT NULL,
PRIMARY KEY (season, game_id, team, half)
);
CREATE INDEX IF NOT EXISTS ix_pbh_team ON silver.nfl_points_by_half (season, team, week);
-- =============================================================================
-- #4: points allowed in the 2nd half, rolled to season-to-date per team.
-- =============================================================================
CREATE OR REPLACE VIEW silver.v_nfl_points_allowed_2nd_half AS
SELECT season, team,
count(DISTINCT game_id) AS games,
sum(points_allowed) AS points_allowed_2nd_half,
round(1.0 * sum(points_allowed) / NULLIF(count(DISTINCT game_id), 0), 2)
AS points_allowed_2nd_half_per_game
FROM silver.nfl_points_by_half
WHERE half = 2
GROUP BY season, team;
-- =============================================================================
-- #6 / #7: individual production -- who gets the sacks, who gets the picks.
-- One row per player per season, on defense.
-- =============================================================================
CREATE OR REPLACE VIEW silver.v_nfl_individual_sacks AS
SELECT d.season, d.team, d.player_id, ANY_VALUE(p.player_display_name) AS player_name,
ANY_VALUE(p.position) AS position,
sum(d.def_sacks) AS sacks,
sum(d.def_qb_hits) AS qb_hits,
sum(d.def_tackles_for_loss) AS tackles_for_loss,
count(DISTINCT d.game_id) AS games,
row_number() OVER (PARTITION BY d.season, d.team ORDER BY sum(d.def_sacks) DESC)
AS sack_rank_on_team
FROM silver.nfl_player_defense_week d
LEFT JOIN silver.nfl_player p ON p.player_id = d.player_id
WHERE d.def_sacks > 0
GROUP BY d.season, d.team, d.player_id
ORDER BY sacks DESC;
CREATE OR REPLACE VIEW silver.v_nfl_individual_interceptions AS
SELECT d.season, d.team, d.player_id, ANY_VALUE(p.player_display_name) AS player_name,
ANY_VALUE(p.position) AS position,
sum(d.def_interceptions) AS interceptions,
sum(d.def_interception_yards) AS interception_yards,
sum(d.def_pass_defended) AS passes_defended,
count(DISTINCT d.game_id) AS games,
row_number() OVER (PARTITION BY d.season, d.team ORDER BY sum(d.def_interceptions) DESC)
AS int_rank_on_team
FROM silver.nfl_player_defense_week d
LEFT JOIN silver.nfl_player p ON p.player_id = d.player_id
WHERE d.def_interceptions > 0
GROUP BY d.season, d.team, d.player_id
ORDER BY interceptions DESC;
-- =============================================================================
-- TEAM-LEVEL ROLLUP -- items #1, #2, #3, #5, #8, one row per team per season.
-- Every "ranking" the user asked for is a rank() computed here, in this table,
-- from real season-to-date totals -- not sourced pre-ranked from anywhere,
-- since nflverse does not publish a rankings table (checked; same reason ATS
-- was computed rather than sourced in sql/nfl_schedule_schema.sql).
-- =============================================================================
CREATE OR REPLACE VIEW silver.v_nfl_team_defense_season AS
WITH tw AS (
SELECT season, team,
count(DISTINCT game_id) AS games,
-- #1 scoring defense input: points allowed. Uses the schedule table
-- (real final scores), not team_week, since points allowed is a
-- game-level fact, not a per-play defensive stat category.
sum(def_sacks) AS sacks,
sum(def_interceptions) AS interceptions,
sum(def_fumbles_forced) AS fumbles_forced,
sum(def_tds) AS def_tds,
sum(def_tackles_for_loss) AS tackles_for_loss,
sum(def_qb_hits) AS qb_hits,
sum(def_pass_defended) AS passes_defended
FROM silver.nfl_team_week
GROUP BY season, team
), sched AS (
-- final scores + win/loss, one row per team per game (both perspectives)
SELECT season, home_team AS team, away_team AS opponent, home_score AS points_scored,
away_score AS points_allowed,
CASE WHEN home_score > away_score THEN 1
WHEN home_score < away_score THEN 0 ELSE NULL END AS win
FROM silver.nfl_schedule WHERE home_score IS NOT NULL
UNION ALL
SELECT season, away_team, home_team, away_score, home_score,
CASE WHEN away_score > home_score THEN 1
WHEN away_score < home_score THEN 0 ELSE NULL END
FROM silver.nfl_schedule WHERE home_score IS NOT NULL
), sched_agg AS (
SELECT season, team,
count(*) AS games_played,
sum(win) AS wins,
count(*) FILTER (WHERE win = 0) AS losses,
sum(points_allowed) AS points_allowed_total,
round(1.0 * sum(points_allowed) / NULLIF(count(*), 0), 2)
AS points_allowed_per_game,
-- #5: team win percentage
round(100.0 * sum(win) / NULLIF(count(*), 0), 1) AS win_pct
FROM sched
GROUP BY season, team
), adv AS (
-- pass-defense context that feeds the IB Score: how a defense is being
-- scored on and how often, per the user's own framing
SELECT season, team,
sum(def_pressures) AS pressures,
sum(def_times_blitzed) AS times_blitzed,
sum(def_times_hurried) AS times_hurried,
sum(def_times_hitqb) AS times_hit_qb,
round(AVG(def_completion_pct), 4) AS completion_pct_allowed,
round(AVG(def_yards_allowed_per_tgt), 3) AS yards_allowed_per_tgt,
round(AVG(def_passer_rating_allowed), 2) AS passer_rating_allowed,
round(AVG(def_adot), 2) AS adot_allowed
FROM silver.nfl_pfr_adv_defense_week
GROUP BY season, team
)
SELECT tw.season, tw.team, sa.games_played,
-- #5
sa.wins, sa.losses, sa.win_pct,
-- #1 (scoring defense)
sa.points_allowed_total, sa.points_allowed_per_game,
rank() OVER (PARTITION BY tw.season ORDER BY sa.points_allowed_per_game ASC)
AS rank_scoring_defense,
-- #2
tw.sacks,
rank() OVER (PARTITION BY tw.season ORDER BY tw.sacks DESC) AS rank_sacks,
-- #3
tw.interceptions,
rank() OVER (PARTITION BY tw.season ORDER BY tw.interceptions DESC) AS rank_interceptions,
-- #8: turnovers created = interceptions + fumbles forced (the standard
-- definition; nflverse does not publish a combined column, so it is
-- summed here from the two components it does publish)
tw.interceptions + tw.fumbles_forced AS turnovers_created,
rank() OVER (PARTITION BY tw.season
ORDER BY tw.interceptions + tw.fumbles_forced DESC)
AS rank_turnovers_created,
-- context that feeds the IB Score
tw.qb_hits, tw.tackles_for_loss, tw.passes_defended, tw.def_tds,
adv.pressures, adv.times_blitzed, adv.times_hurried, adv.times_hit_qb,
adv.completion_pct_allowed, adv.yards_allowed_per_tgt,
adv.passer_rating_allowed, adv.adot_allowed,
ph.points_allowed_2nd_half, ph.points_allowed_2nd_half_per_game
FROM tw
JOIN sched_agg sa ON sa.season = tw.season AND sa.team = tw.team
LEFT JOIN adv ON adv.season = tw.season AND adv.team = tw.team
LEFT JOIN silver.v_nfl_points_allowed_2nd_half ph
ON ph.season = tw.season AND ph.team = tw.team;
-- =============================================================================
-- THE IB SCORE (Irritable Bowel Score) -- quarterback comfort against a given
-- defense, on the 1-5 scale the user defined:
--
-- CAT 1 no pressure up the middle all day. RB over 100 rushing, multiple
-- WRs over 60 yards, 4 passing TDs -- the QB is comfortable and it
-- shows up in every stat line at once.
-- CAT 5 irritated and aggravated all game -- constant pressure, nothing
-- comes easy.
--
-- Per the user: "the evidence is in the stats -- how they are being scored on
-- and how often." So the score is not a vibe, it's five components, each
-- normalized against the rest of the league this season and averaged:
--
-- PRESSURE def_pressures + def_times_hitqb + sacks, per game
-- COVERAGE completion_pct_allowed, yards_allowed_per_tgt, passer_rating_
-- allowed -- can this defense actually stop the throw once
-- pressure gets there
-- TAKEAWAYS interceptions + fumbles forced, per game -- a defense that
-- creates turnovers is a defense a QB has to play scared of
-- RUN STOP inverse of rushing yards allowed per game (from nfl_team_week)
-- -- if the run is live, the QB never faces a one-dimensional
-- defense and stays comfortable longer
-- SCORING points allowed per game, and specifically 2nd half -- does
-- this defense fold as the game goes on
--
-- Each component is scaled 0 (least toxic) to 100 (most toxic) against this
-- season's real league range, THEN AVERAGED, THEN mapped onto 1-5. Nothing is
-- guessed -- every input is a season-to-date real stat, and the scaling
-- bounds move automatically as more of the season is played, per the user's
-- point that "as the season goes it will all fall in line."
-- =============================================================================
CREATE OR REPLACE VIEW gold.v_nfl_defense_ib_score AS
WITH d AS (
SELECT t.*,
tw.rushing_yards AS rushing_yards_allowed_raw
FROM silver.v_nfl_team_defense_season t
LEFT JOIN (SELECT season, opponent_team AS team, sum(rushing_yards) AS rushing_yards
FROM silver.nfl_team_week GROUP BY season, opponent_team) tw
ON tw.season = t.season AND tw.team = t.team
), scaled AS (
SELECT d.*,
round(1.0 * d.rushing_yards_allowed_raw / NULLIF(d.games_played, 0), 2)
AS rushing_yards_allowed_per_game,
-- pressure per game: sacks + hits + tracked pressures, blended so a
-- defense that hits the QB without a sack still reads as toxic
round(1.0 * (d.sacks + d.qb_hits + COALESCE(d.pressures, 0))
/ NULLIF(d.games_played, 0), 2) AS pressure_events_per_game,
round(1.0 * d.turnovers_created / NULLIF(d.games_played, 0), 2)
AS turnovers_per_game
FROM d
), rng AS (
-- league min/max THIS SEASON, recomputed every run -- the scaling bounds
-- move as more games are played, which is the point.
SELECT season,
min(pressure_events_per_game) AS p_min, max(pressure_events_per_game) AS p_max,
min(COALESCE(passer_rating_allowed, 0)) AS pr_min,
max(COALESCE(passer_rating_allowed, 200)) AS pr_max,
min(turnovers_per_game) AS to_min, max(turnovers_per_game) AS to_max,
min(rushing_yards_allowed_per_game) AS ry_min, max(rushing_yards_allowed_per_game) AS ry_max,
min(points_allowed_per_game) AS pa_min, max(points_allowed_per_game) AS pa_max,
min(COALESCE(points_allowed_2nd_half_per_game, points_allowed_per_game/2))
AS pa2_min,
max(COALESCE(points_allowed_2nd_half_per_game, points_allowed_per_game/2))
AS pa2_max
FROM scaled GROUP BY season
), norm AS (
SELECT s.*,
-- 0..100, HIGHER = MORE TOXIC on every component
round(100 * (s.pressure_events_per_game - r.p_min)
/ NULLIF(r.p_max - r.p_min, 0), 1) AS pressure_component,
round(100 * (1 - (COALESCE(s.passer_rating_allowed, r.pr_max) - r.pr_min)
/ NULLIF(r.pr_max - r.pr_min, 0)), 1) AS coverage_component,
round(100 * (s.turnovers_per_game - r.to_min)
/ NULLIF(r.to_max - r.to_min, 0), 1) AS takeaway_component,
round(100 * (1 - (s.rushing_yards_allowed_per_game - r.ry_min)
/ NULLIF(r.ry_max - r.ry_min, 0)), 1) AS run_stop_component,
round(100 * (1 - (s.points_allowed_per_game - r.pa_min)
/ NULLIF(r.pa_max - r.pa_min, 0)), 1) AS scoring_component,
round(100 * (1 - (COALESCE(s.points_allowed_2nd_half_per_game,
s.points_allowed_per_game/2) - r.pa2_min)
/ NULLIF(r.pa2_max - r.pa2_min, 0)), 1) AS second_half_component
FROM scaled s JOIN rng r ON r.season = s.season
)
SELECT season, team, games_played, wins, losses, win_pct,
rank_scoring_defense, rank_sacks, rank_interceptions, rank_turnovers_created,
points_allowed_per_game, points_allowed_2nd_half_per_game,
sacks, interceptions, turnovers_created,
pressure_component, coverage_component, takeaway_component,
run_stop_component, scoring_component, second_half_component,
round((pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0, 1)
AS toxicity_index_0_100,
-- map the 0-100 toxicity index onto the user's 1-5 category scale
CASE
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 20
THEN 1
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 40
THEN 2
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 60
THEN 3
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 80
THEN 4
ELSE 5
END AS ib_score,
CASE
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 20
THEN 'CAT 1 -- comfortable: clean pocket, run is live, big game likely'
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 40
THEN 'CAT 2 -- mostly comfortable'
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 60
THEN 'CAT 3 -- mixed, game-plan dependent'
WHEN (pressure_component + coverage_component + takeaway_component
+ run_stop_component + scoring_component + second_half_component) / 6.0 < 80
THEN 'CAT 4 -- uncomfortable, expect pressure'
ELSE 'CAT 5 -- irritated all game: heavy pressure, takeaways likely'
END AS ib_category
FROM norm
ORDER BY toxicity_index_0_100 DESC;
-- =============================================================================
-- THE JOIN THIS POND EXISTS FOR: pair a defense's toxicity against the offense
-- it is about to face, using the usage-pond players on that offense. When the
-- #1 target on a top offense (a name a book uses as bait, per the user) is
-- about to face a high-IB-score defense, that is the mismatch this lake was
-- built to catch.
-- =============================================================================
CREATE OR REPLACE VIEW gold.v_nfl_matchup_toxicity AS
SELECT s.game_id, s.season, s.week, s.game_date,
s.away_team, s.home_team,
ha.ib_score AS home_defense_ib_score, ha.ib_category AS home_defense_ib_category,
aa.ib_score AS away_defense_ib_score, aa.ib_category AS away_defense_ib_category,
-- the away offense faces the HOME defense, and vice versa
ha.toxicity_index_0_100 AS home_defense_toxicity,
aa.toxicity_index_0_100 AS away_defense_toxicity
FROM silver.nfl_schedule s
LEFT JOIN gold.v_nfl_defense_ib_score ha ON ha.season = s.season AND ha.team = s.home_team
LEFT JOIN gold.v_nfl_defense_ib_score aa ON aa.season = s.season AND aa.team = s.away_team;
|