Spaces:
Paused
Paused
| -- ============================================================================= | |
| -- 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; | |