Spaces:
Paused
Paused
| -- ============================================================================= | |
| -- frontal-lobe2 : player usage & situational-confidence pond (Phase 1) | |
| -- ============================================================================= | |
| -- Built the way the user built it: identify TWO stats first -- the spine -- then | |
| -- layer every other stat on top of that spine. The layers are only meaningful | |
| -- because the spine decides who we are even asking about. | |
| -- | |
| -- LAYER 1 THE SPINE -- position-gated opportunity. "Who does he go to?" | |
| -- if WR or TE -> TARGETS | |
| -- if RB or FB -> RUSH ATTEMPTS | |
| -- Nothing else. A WR's confidence is measured in targets and a | |
| -- back's in carries, because those are the touches the coach | |
| -- actually assigns. Mixing them into one "touches" number lets a | |
| -- checkdown back outrank a WR1 and tells you nothing. | |
| -- | |
| -- LAYER 2 STRESS -- the user's core insight, quoted: | |
| -- "the higher the stress level the more they go to who they know" | |
| -- So the signal is not how big a share he gets. It is whether his | |
| -- share GROWS as the situation tightens. A coach spreading the ball | |
| -- around on 1st-and-10 in the 2nd quarter is calling an offense. A | |
| -- coach going to the same man on 3rd-and-4 at the 8 with a | |
| -- three-point lead in the 4th is telling you who he trusts. | |
| -- Measured as: share under high stress MINUS share under low | |
| -- stress. That delta is the indicator. | |
| -- | |
| -- LAYER 3 THE DEPTH CHART -- the coach's own published hierarchy, compared | |
| -- to what the ball actually says. Out-earning your listed slot is | |
| -- the most perishable signal here, because the chart is public and | |
| -- slow while usage is real and fast. | |
| -- | |
| -- LAYER 4 WHERE -- field zone (Inside 20/10/5), pass location, air yards. | |
| -- "in the red zone, where were the passes thrown." | |
| -- | |
| -- LAYER 5 RETURN -- Yards From Scrimmage / yards per touch. PFR's | |
| -- /years/2026/scrimmage.htm. "productive players once they touch | |
| -- the ball." Reused from silver.nfl_player_scrimmage_week so a | |
| -- touch means one thing in this lake. | |
| -- | |
| -- EVERY SHARE IS INTRA-TEAM. Per the user: the measurement is confidence | |
| -- "within his own team", not across the NFL. A WR on a pass-heavy offense | |
| -- out-targets a trusted WR1 on a run-first team without either fact saying | |
| -- anything about the two coaches. So every denominator below is that player's | |
| -- OWN TEAM's total in that same game. | |
| -- | |
| -- SOURCES -- nflverse only (CC-BY-4.0, nightly). No new vendor needed: | |
| -- pbp play_by_play_2026.csv.gz -> every target and every carry, | |
| -- with down, distance, clock, score, field position. Same file | |
| -- ingest_nfl_td_logs.py already pulls. | |
| -- depth_charts depth_charts_2026.csv -> the stated hierarchy, | |
| -- point-in-time (197 snapshots Mar->Sep 2026, verified). | |
| -- | |
| -- PFR is never fetched (403 Cloudflare + ToS). docs/PFR_SOURCE_NOTE.md. | |
| -- ffverse/ffopportunity expected-points is verified and real but deliberately | |
| -- NOT used in Phase 1: it is GPL-3.0, weekly rather than nightly, and a model | |
| -- output rather than an observed fact. (game_id, play_id) is preserved below | |
| -- so it can be joined 1:1 as a Phase 2 enrichment. | |
| -- | |
| -- Engine: DuckDB. Depends on sql/nfl_pfr_schema.sql and | |
| -- sql/nfl_schedule_schema.sql. | |
| -- ============================================================================= | |
| CREATE SCHEMA IF NOT EXISTS silver; | |
| CREATE SCHEMA IF NOT EXISTS gold; | |
| -- ============================================================================= | |
| -- BASE TABLE: one row per OPPORTUNITY -- a target or a carry. | |
| -- | |
| -- GRAIN: (game_id, play_id, opportunity_type). Same event-grain philosophy as | |
| -- silver.nfl_td_log: store the decision once, express every chart as a view. | |
| -- A single play can be only one of the two, so in practice this is one row per | |
| -- play, but the composite key keeps it honest. | |
| -- | |
| -- An "opportunity" is a coaching decision to put the ball in a named player's | |
| -- hands. Sacks, scrambles, throwaways and spikes have no intended recipient in | |
| -- the data and are excluded -- they are not decisions to trust anyone. | |
| -- ============================================================================= | |
| CREATE TABLE IF NOT EXISTS silver.nfl_opportunity_event ( | |
| season INTEGER NOT NULL, | |
| week INTEGER NOT NULL, | |
| season_type VARCHAR, | |
| game_id VARCHAR NOT NULL, | |
| play_id INTEGER NOT NULL, | |
| opportunity_type VARCHAR NOT NULL, -- 'target' | 'rush' | |
| -- who | |
| posteam VARCHAR NOT NULL, -- the offense; whose coach we are reading | |
| defteam VARCHAR, | |
| player_id VARCHAR, -- receiver on a target, rusher on a carry | |
| player_name VARCHAR, | |
| passer_player_id VARCHAR, -- NULL on a rush | |
| passer_player_name VARCHAR, | |
| -- ---- LAYER 4: WHERE ------------------------------------------------ | |
| yardline_100 INTEGER, -- yards from the opponent goal line | |
| is_inside_20 BOOLEAN, -- PFR "Inside 20" | |
| is_inside_10 BOOLEAN, -- PFR "Inside 10" | |
| is_inside_5 BOOLEAN, -- PFR "Inside 5" | |
| is_goal_to_go BOOLEAN, | |
| air_yards INTEGER, -- targets only | |
| pass_location VARCHAR, -- left | middle | right (targets) | |
| run_location VARCHAR, -- left | middle | right (rushes) | |
| run_gap VARCHAR, -- guard | tackle | end (rushes) | |
| -- ---- LAYER 2: STRESS ---------------------------------------------- | |
| -- The situation as the play was called. All four inputs come straight | |
| -- from pbp; none are inferred. | |
| down INTEGER, | |
| ydstogo INTEGER, | |
| qtr INTEGER, | |
| game_seconds_remaining INTEGER, | |
| score_differential INTEGER, -- offense minus defense, pre-play | |
| -- The stress ladder. Documented explicitly so it can be argued with | |
| -- rather than reverse-engineered: | |
| -- 3 MAX -- goal to go, inside the 5, 4th down, or a one-score | |
| -- game in the last 5 minutes. No margin for error. | |
| -- 2 HIGH -- red zone, 3rd down, or 2 yards or fewer to go. | |
| -- Conversion or scoring pressure. | |
| -- 1 MODERATE -- 2nd and long, or trailing by 9-16 (two-score chase). | |
| -- 0 NEUTRAL -- everything else. Open field, script territory. | |
| stress_level INTEGER NOT NULL, | |
| is_high_stress BOOLEAN NOT NULL, -- stress_level >= 2 | |
| is_two_minute BOOLEAN, -- last 2 min of either half | |
| -- QB run type. A designed keeper or sneak is a CALLED PLAY -- the coach | |
| -- chose the quarterback over his back, which is a confidence statement and | |
| -- belongs in the pool. A scramble is a collapsed pocket: nobody decided to | |
| -- give him the ball. Flagged, not deleted, so either reading is available. | |
| is_qb_scramble BOOLEAN, | |
| is_designed_run BOOLEAN, -- rush that was not a scramble | |
| -- ---- outcome (Layer 5 inputs) -------------------------------------- | |
| completed BOOLEAN, -- complete_pass; NULL-ish on rush (true) | |
| yards_gained INTEGER, | |
| yards_after_catch INTEGER, | |
| touchdown BOOLEAN, | |
| first_down BOOLEAN, | |
| turnover BOOLEAN, -- interception on a target | |
| play_description VARCHAR, | |
| ingest_run_id VARCHAR NOT NULL, | |
| PRIMARY KEY (season, game_id, play_id, opportunity_type) | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_opp_player ON silver.nfl_opportunity_event (season, player_id, week); | |
| CREATE INDEX IF NOT EXISTS ix_opp_team ON silver.nfl_opportunity_event (season, posteam, week); | |
| CREATE INDEX IF NOT EXISTS ix_opp_stress ON silver.nfl_opportunity_event (season, posteam, is_high_stress); | |
| CREATE INDEX IF NOT EXISTS ix_opp_type ON silver.nfl_opportunity_event (season, posteam, opportunity_type); | |
| -- ============================================================================= | |
| -- BASE TABLE: depth chart snapshots (Layer 3) -- the coach's stated hierarchy. | |
| -- | |
| -- This source is a POINT-IN-TIME snapshot series, not a weekly table. A depth | |
| -- chart read today says nothing about who the WR2 was in week 1, so every | |
| -- join below goes through v_nfl_depth_chart_pregame, which takes the latest | |
| -- snapshot at or before that game's kickoff. Same point-in-time discipline the | |
| -- props table enforces with is_pregame -- and for the same reason: otherwise a | |
| -- mid-week promotion leaks backwards into a metric describing a past decision. | |
| -- | |
| -- snapshot_at is TIMESTAMPTZ deliberately. The source publishes ISO-8601 with | |
| -- a literal Z; storing that naive is exactly the bug already found and fixed | |
| -- on gold.nfl_prop_line_rotowire, where local time sat in a column named _utc. | |
| -- ============================================================================= | |
| CREATE TABLE IF NOT EXISTS silver.nfl_depth_chart_snapshot ( | |
| snapshot_at TIMESTAMPTZ NOT NULL, | |
| team VARCHAR NOT NULL, | |
| pos_grp VARCHAR, -- '3WR 1TE' = offense | |
| pos_abb VARCHAR NOT NULL, -- QB | RB | WR | TE | FB | OL... | |
| pos_name VARCHAR, | |
| pos_slot INTEGER, | |
| pos_rank INTEGER NOT NULL, -- 1 = starter at that spot | |
| gsis_id VARCHAR, -- joins silver.nfl_player.player_id | |
| espn_id VARCHAR, | |
| player_name VARCHAR NOT NULL, | |
| ingest_run_id VARCHAR NOT NULL, | |
| PRIMARY KEY (snapshot_at, team, pos_abb, pos_rank, player_name) | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_depth_team_at ON silver.nfl_depth_chart_snapshot (team, snapshot_at); | |
| CREATE INDEX IF NOT EXISTS ix_depth_gsis ON silver.nfl_depth_chart_snapshot (gsis_id, snapshot_at); | |
| -- ============================================================================= | |
| -- LAYER 1 -- THE SPINE | |
| -- ============================================================================= | |
| -- Team opportunity totals: the denominator for every share in this pond. Split | |
| -- by type, because the spine is position-gated -- a WR's share is of the | |
| -- team's TARGETS, a back's is of the team's CARRIES. One definition, one place. | |
| -- QB DESIGNED RUNS ARE IN THE RUSH POOL. Per the user, and he is right: | |
| -- | |
| -- "in that situation the team is inside of the 5 yardline therefore qb if he | |
| -- runs is counted as a running back in my system, because the actual rb is | |
| -- competing with the qb in an area where he should get the ball and that too | |
| -- is a measurement of the coaches confidence" | |
| -- | |
| -- An earlier version of this view excluded QB carries, on the theory that the | |
| -- goal-line sneak was a structural artifact crushing every back's stress share. | |
| -- That was wrong, and it deleted the signal instead of measuring it. If a coach | |
| -- is inside the 5 -- the one place a starting back should get the ball -- and | |
| -- calls a keeper instead, that IS the confidence statement. A back's share | |
| -- falling under stress because his quarterback takes the sneak is the finding, | |
| -- not a bug obscuring one. | |
| -- | |
| -- The one distinction kept: a DESIGNED run (sneak, keeper, draw) is a called | |
| -- play, so it counts. A SCRAMBLE is a collapsed pocket -- nobody chose to give | |
| -- him the ball -- so it does not. Both stay in the event table, flagged, so | |
| -- this choice is auditable rather than baked in. | |
| -- | |
| -- The user's own cross-check on whether this unfairly buries starters: | |
| -- "it will not effect the starting RB overall because if you look at the | |
| -- rushing inside the 20 and success rate then more than likely it is going | |
| -- to the starting rb" | |
| -- That check is v_nfl_redzone_tiers (inside-20 opportunities and TDs), which is | |
| -- exactly why the tiers live alongside the stress split rather than inside it. | |
| CREATE OR REPLACE VIEW silver.v_nfl_team_opportunity_volume AS | |
| WITH e AS ( | |
| SELECT o.*, COALESCE(p.position, 'UNK') AS position | |
| FROM silver.nfl_opportunity_event o | |
| LEFT JOIN silver.nfl_player p ON p.player_id = o.player_id | |
| -- scrambles are not a coaching decision; everything else counts | |
| WHERE o.opportunity_type = 'target' OR COALESCE(o.is_designed_run, true) | |
| ) | |
| SELECT season, week, game_id, posteam AS team, | |
| count(*) FILTER (WHERE opportunity_type = 'target') AS team_targets, | |
| count(*) FILTER (WHERE opportunity_type = 'rush') AS team_rushes, | |
| count(*) FILTER (WHERE opportunity_type = 'target' AND is_high_stress) | |
| AS team_targets_stress, | |
| count(*) FILTER (WHERE opportunity_type = 'rush' AND is_high_stress) | |
| AS team_rushes_stress, | |
| count(*) FILTER (WHERE opportunity_type = 'target' AND NOT is_high_stress) | |
| AS team_targets_calm, | |
| count(*) FILTER (WHERE opportunity_type = 'rush' AND NOT is_high_stress) | |
| AS team_rushes_calm, | |
| count(*) FILTER (WHERE is_inside_20) AS team_opps_inside_20, | |
| count(*) FILTER (WHERE is_inside_10) AS team_opps_inside_10, | |
| count(*) FILTER (WHERE is_inside_5) AS team_opps_inside_5, | |
| count(*) FILTER (WHERE is_goal_to_go) AS team_opps_g2g, | |
| count(*) FILTER (WHERE stress_level = 3) AS team_opps_max_stress, | |
| -- the QB's slice of the pool, surfaced rather than hidden: this is the | |
| -- number that explains a back's stress share, so it should be readable | |
| -- next to it instead of buried in a denominator | |
| count(*) FILTER (WHERE opportunity_type = 'rush' AND position = 'QB') | |
| AS team_qb_designed_runs, | |
| count(*) FILTER (WHERE opportunity_type = 'rush' AND position = 'QB' | |
| AND is_inside_5) AS team_qb_runs_inside_5, | |
| count(*) FILTER (WHERE opportunity_type = 'rush' AND position = 'QB' | |
| AND is_high_stress) AS team_qb_runs_stress | |
| FROM e | |
| GROUP BY season, week, game_id, posteam; | |
| -- THE SPINE ITSELF. One row per player per game, carrying only the | |
| -- position-appropriate opportunity metric and its intra-team share. | |
| -- | |
| -- spine_metric names which stat is being counted so nothing downstream has to | |
| -- guess, and so a mis-gated position is visible rather than silent. | |
| CREATE OR REPLACE VIEW silver.v_nfl_opportunity_spine AS | |
| WITH counted AS ( | |
| SELECT e.season, e.week, e.game_id, e.posteam AS team, e.player_id, | |
| ANY_VALUE(e.player_name) AS player_name, | |
| count(*) FILTER (WHERE e.opportunity_type = 'target') AS targets, | |
| count(*) FILTER (WHERE e.opportunity_type = 'rush') AS rushes, | |
| count(*) FILTER (WHERE e.opportunity_type = 'target' AND e.is_high_stress) | |
| AS targets_stress, | |
| count(*) FILTER (WHERE e.opportunity_type = 'rush' AND e.is_high_stress) | |
| AS rushes_stress, | |
| count(*) FILTER (WHERE e.opportunity_type = 'target' AND NOT e.is_high_stress) | |
| AS targets_calm, | |
| count(*) FILTER (WHERE e.opportunity_type = 'rush' AND NOT e.is_high_stress) | |
| AS rushes_calm | |
| FROM silver.nfl_opportunity_event e | |
| WHERE e.player_id IS NOT NULL | |
| -- scrambles excluded from a player's own count too, matching the | |
| -- denominator in v_nfl_team_opportunity_volume | |
| AND (e.opportunity_type = 'target' OR COALESCE(e.is_designed_run, true)) | |
| GROUP BY e.season, e.week, e.game_id, e.posteam, e.player_id | |
| ) | |
| -- THE GATE | |
| -- WR, TE -> targets | |
| -- RB, FB -> rush attempts | |
| -- QB -> rush attempts, per the user: "qb if he runs is counted as a | |
| -- running back in my system". Labeled rush_attempts_qb so a | |
| -- quarterback appearing high on a rushing leaderboard is obviously | |
| -- a quarterback and not a mislabel -- when Hurts tops Philadelphia | |
| -- inside the 5, that is the actual finding, not an error. | |
| -- | |
| -- No inference. An earlier version fell back to "whichever stat he accumulated | |
| -- more of" for unlabeled positions, which silently pulled QBs in under a name | |
| -- that looked like a real gate. Guessing the gate defeats having one. | |
| SELECT c.season, c.week, c.game_id, c.team, c.player_id, c.player_name, | |
| p.position, | |
| CASE WHEN p.position = 'QB' THEN 'rush_attempts_qb' | |
| WHEN p.position IN ('RB', 'FB') THEN 'rush_attempts' | |
| ELSE 'targets' END AS spine_metric, | |
| CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN c.rushes | |
| ELSE c.targets END AS spine_opportunities, | |
| CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN c.rushes_stress | |
| ELSE c.targets_stress END AS spine_opps_stress, | |
| CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN c.rushes_calm | |
| ELSE c.targets_calm END AS spine_opps_calm, | |
| -- the matching team denominator for that same metric | |
| CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN v.team_rushes | |
| ELSE v.team_targets END AS team_spine_opportunities, | |
| CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN v.team_rushes_stress | |
| ELSE v.team_targets_stress END AS team_spine_opps_stress, | |
| CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN v.team_rushes_calm | |
| ELSE v.team_targets_calm END AS team_spine_opps_calm, | |
| -- both raw counts kept: a receiving back's targets still matter, they | |
| -- are just not his spine | |
| c.targets, c.rushes, | |
| -- the QB's share of this team's rush pool, carried on every row. This is | |
| -- the number that EXPLAINS a back's stress share, so it should be | |
| -- readable beside it rather than buried in a denominator. | |
| v.team_qb_designed_runs, v.team_qb_runs_inside_5, v.team_qb_runs_stress | |
| FROM counted c | |
| LEFT JOIN silver.nfl_player p ON p.player_id = c.player_id | |
| JOIN silver.v_nfl_team_opportunity_volume v | |
| ON v.game_id = c.game_id AND v.team = c.team | |
| WHERE p.position IN ('WR', 'TE', 'RB', 'FB', 'QB'); | |
| -- ============================================================================= | |
| -- LAYER 2 -- STRESS. The heart of the indicator. | |
| -- | |
| -- "the higher the stress level the more they go to who they know" | |
| -- | |
| -- confidence_delta = his share of the team's spine opportunities when the | |
| -- situation is tight, MINUS his share when it is not. Positive means the coach | |
| -- concentrates on him under pressure. That is the measurement. | |
| -- | |
| -- Read alongside spine_opps_stress: a delta computed off 2 high-stress | |
| -- opportunities is noise, which is why the raw counts travel with it and why | |
| -- the indicator applies shrinkage rather than trusting the ratio directly. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW silver.v_nfl_stress_split AS | |
| SELECT season, team, player_id, | |
| ANY_VALUE(player_name) AS player_name, | |
| ANY_VALUE(spine_metric) AS spine_metric, | |
| count(DISTINCT game_id) AS games, | |
| sum(spine_opportunities) AS spine_opps, | |
| sum(team_spine_opportunities) AS team_spine_opps, | |
| sum(spine_opps_stress) AS spine_opps_stress, | |
| sum(team_spine_opps_stress) AS team_spine_opps_stress, | |
| sum(spine_opps_calm) AS spine_opps_calm, | |
| sum(team_spine_opps_calm) AS team_spine_opps_calm, | |
| -- overall intra-team share of his own position's opportunity pool | |
| round(1.0 * sum(spine_opportunities) | |
| / NULLIF(sum(team_spine_opportunities), 0), 4) AS share_overall, | |
| -- share when it is tight, and when it is not | |
| round(1.0 * sum(spine_opps_stress) | |
| / NULLIF(sum(team_spine_opps_stress), 0), 4) AS share_stress, | |
| round(1.0 * sum(spine_opps_calm) | |
| / NULLIF(sum(team_spine_opps_calm), 0), 4) AS share_calm, | |
| -- THE SIGNAL | |
| round(1.0 * sum(spine_opps_stress) / NULLIF(sum(team_spine_opps_stress), 0) | |
| - 1.0 * sum(spine_opps_calm) / NULLIF(sum(team_spine_opps_calm), 0), 4) | |
| AS confidence_delta | |
| FROM silver.v_nfl_opportunity_spine | |
| GROUP BY season, team, player_id; | |
| -- ============================================================================= | |
| -- LAYER 4 -- WHERE. Field-zone tiers, shaped like PFR's red-zone pages | |
| -- (/years/2026/redzone-passing.htm, redzone-rushing.htm): three nested tiers, | |
| -- each with opportunities, yards and TDs. | |
| -- | |
| -- The "%" column on those PFR pages is a TEAM share, which is also the only | |
| -- version that answers this pond's question -- a league-wide red-zone | |
| -- percentage just ranks offenses. Named _intra so that is unambiguous. | |
| -- | |
| -- _shrunk columns exist because red-zone samples are genuinely tiny. Confirmed | |
| -- on real weeks 1-2 data: teams had between 0 and 18 inside-20 opportunities | |
| -- for the entire season so far. A raw share on a denominator of 5 is mostly | |
| -- noise -- 2-of-5 reads as 40% and would outrank a far more clearly trusted | |
| -- player at 6-of-18. Adding k=4 to the denominator pulls small samples toward | |
| -- zero until volume earns the number, and fades to irrelevance as real | |
| -- denominators grow. Raw and shrunk are both exposed so the adjustment is | |
| -- always visible and reversible. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW silver.v_nfl_redzone_tiers AS | |
| WITH pl AS ( | |
| SELECT season, posteam AS team, player_id, | |
| ANY_VALUE(player_name) AS player_name, | |
| ANY_VALUE(opportunity_type) AS opportunity_type, | |
| count(*) FILTER (WHERE is_inside_20) AS opp_i20, | |
| sum(CASE WHEN is_inside_20 THEN yards_gained ELSE 0 END) AS yds_i20, | |
| count(*) FILTER (WHERE is_inside_20 AND touchdown) AS td_i20, | |
| count(*) FILTER (WHERE is_inside_10) AS opp_i10, | |
| sum(CASE WHEN is_inside_10 THEN yards_gained ELSE 0 END) AS yds_i10, | |
| count(*) FILTER (WHERE is_inside_10 AND touchdown) AS td_i10, | |
| count(*) FILTER (WHERE is_inside_5) AS opp_i5, | |
| sum(CASE WHEN is_inside_5 THEN yards_gained ELSE 0 END) AS yds_i5, | |
| count(*) FILTER (WHERE is_inside_5 AND touchdown) AS td_i5, | |
| count(*) FILTER (WHERE is_goal_to_go) AS opp_g2g, | |
| count(*) FILTER (WHERE is_goal_to_go AND touchdown) AS td_g2g | |
| FROM silver.nfl_opportunity_event | |
| WHERE player_id IS NOT NULL | |
| GROUP BY season, posteam, player_id | |
| ), tm AS ( | |
| SELECT season, posteam AS team, | |
| count(*) FILTER (WHERE is_inside_20) AS t_i20, | |
| count(*) FILTER (WHERE is_inside_10) AS t_i10, | |
| count(*) FILTER (WHERE is_inside_5) AS t_i5, | |
| count(*) FILTER (WHERE is_goal_to_go) AS t_g2g | |
| FROM silver.nfl_opportunity_event | |
| GROUP BY season, posteam | |
| ) | |
| SELECT pl.season, pl.team, pl.player_id, pl.player_name, pl.opportunity_type, | |
| tm.t_i20 AS team_opp_i20, tm.t_i10 AS team_opp_i10, tm.t_i5 AS team_opp_i5, | |
| pl.opp_i20, pl.yds_i20, pl.td_i20, | |
| round(1.0 * pl.opp_i20 / NULLIF(tm.t_i20, 0), 4) AS pct_i20_intra, | |
| round(1.0 * pl.opp_i20 / NULLIF(tm.t_i20 + 4, 0), 4) AS pct_i20_shrunk, | |
| pl.opp_i10, pl.yds_i10, pl.td_i10, | |
| round(1.0 * pl.opp_i10 / NULLIF(tm.t_i10, 0), 4) AS pct_i10_intra, | |
| round(1.0 * pl.opp_i10 / NULLIF(tm.t_i10 + 4, 0), 4) AS pct_i10_shrunk, | |
| pl.opp_i5, pl.yds_i5, pl.td_i5, | |
| round(1.0 * pl.opp_i5 / NULLIF(tm.t_i5, 0), 4) AS pct_i5_intra, | |
| round(1.0 * pl.opp_i5 / NULLIF(tm.t_i5 + 4, 0), 4) AS pct_i5_shrunk, | |
| pl.opp_g2g, pl.td_g2g, | |
| round(1.0 * pl.opp_g2g / NULLIF(tm.t_g2g + 4, 0), 4) AS pct_g2g_shrunk | |
| FROM pl JOIN tm ON tm.season = pl.season AND tm.team = pl.team | |
| WHERE pl.opp_i20 > 0; | |
| -- Where on the field the ball went: pass location x air-yard depth for | |
| -- targets, run location x gap for carries. One view, one row per bucket. | |
| CREATE OR REPLACE VIEW silver.v_nfl_opportunity_location_map AS | |
| SELECT season, posteam AS team, player_id, | |
| ANY_VALUE(player_name) AS player_name, | |
| opportunity_type, | |
| COALESCE(pass_location, run_location, 'unknown') AS field_side, | |
| CASE WHEN opportunity_type = 'rush' THEN COALESCE(run_gap, 'no_gap_recorded') | |
| WHEN air_yards IS NULL THEN 'unknown' | |
| WHEN air_yards < 0 THEN 'behind_los' | |
| WHEN air_yards <= 5 THEN 'short_0_5' | |
| WHEN air_yards <= 10 THEN 'short_6_10' | |
| WHEN air_yards <= 20 THEN 'intermediate_11_20' | |
| ELSE 'deep_21_plus' END AS depth_or_gap, | |
| count(*) AS opportunities, | |
| count(*) FILTER (WHERE is_high_stress) AS opportunities_stress, | |
| sum(yards_gained) AS yards, | |
| count(*) FILTER (WHERE touchdown) AS tds | |
| FROM silver.nfl_opportunity_event | |
| WHERE player_id IS NOT NULL | |
| GROUP BY season, posteam, player_id, opportunity_type, field_side, depth_or_gap; | |
| -- ============================================================================= | |
| -- LAYER 3 -- the depth chart, resolved point-in-time per game. | |
| -- Kickoff derived the way ingest_rotowire_props.py does it: schedule gametime | |
| -- is published in US/Eastern, converted to a real instant. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW silver.v_nfl_depth_chart_pregame AS | |
| WITH kick AS ( | |
| SELECT game_id, season, week, home_team AS team, | |
| timezone('America/New_York', game_date + CAST(game_time_local AS TIME)) AS kickoff_utc | |
| FROM silver.nfl_schedule | |
| UNION ALL | |
| SELECT game_id, season, week, away_team, | |
| timezone('America/New_York', game_date + CAST(game_time_local AS TIME)) | |
| FROM silver.nfl_schedule | |
| ), best AS ( | |
| SELECT k.game_id, k.season, k.week, k.team, k.kickoff_utc, | |
| max(d.snapshot_at) AS snapshot_at | |
| FROM kick k | |
| JOIN silver.nfl_depth_chart_snapshot d | |
| ON d.team = k.team AND d.snapshot_at <= k.kickoff_utc | |
| GROUP BY k.game_id, k.season, k.week, k.team, k.kickoff_utc | |
| ) | |
| SELECT b.game_id, b.season, b.week, b.team, b.kickoff_utc, b.snapshot_at, | |
| d.pos_abb, d.pos_name, d.pos_rank, d.gsis_id AS player_id, d.player_name | |
| FROM best b | |
| JOIN silver.nfl_depth_chart_snapshot d | |
| ON d.team = b.team AND d.snapshot_at = b.snapshot_at | |
| WHERE d.pos_grp = '3WR 1TE' | |
| AND d.pos_abb IN ('QB', 'RB', 'WR', 'TE', 'FB'); | |
| -- ============================================================================= | |
| -- COMPONENTS -- all five layers, facts only, no weighting. | |
| -- | |
| -- Kept separate from the indicator on purpose: these are observations. If the | |
| -- weights below are ever retuned or discarded, nothing in this view moves. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW gold.v_nfl_player_usage_components AS | |
| WITH scrim AS ( | |
| -- LAYER 5: Yards From Scrimmage. Reused, not recomputed. | |
| SELECT s.season, s.team, s.player_id, | |
| sum(s.touches) AS touches, | |
| sum(s.scrimmage_yards) AS scrimmage_yards, | |
| sum(s.scrimmage_tds) AS scrimmage_tds, | |
| round(1.0 * sum(s.scrimmage_yards) | |
| / NULLIF(sum(s.touches), 0), 2) AS yards_per_touch, | |
| round(1.0 * sum(s.scrimmage_yards) | |
| / NULLIF(sum(t.team_scrimmage_yards), 0), 4) AS scrimmage_yards_share_intra | |
| FROM silver.nfl_player_scrimmage_week s | |
| JOIN (SELECT season, game_id, team, sum(scrimmage_yards) AS team_scrimmage_yards | |
| FROM silver.nfl_player_scrimmage_week GROUP BY 1,2,3) t | |
| ON t.game_id = s.game_id AND t.team = s.team | |
| WHERE s.touches > 0 | |
| GROUP BY s.season, s.team, s.player_id | |
| ), depth AS ( | |
| SELECT season, team, player_id, | |
| arg_max(pos_abb, week) AS depth_pos, | |
| arg_max(pos_rank, week) AS depth_rank, | |
| max(week) AS depth_as_of_week | |
| FROM silver.v_nfl_depth_chart_pregame | |
| WHERE player_id IS NOT NULL | |
| GROUP BY season, team, player_id | |
| ) | |
| SELECT ss.season, ss.team, ss.player_id, | |
| COALESCE(ss.player_name, p.player_display_name) AS player_name, | |
| p.position, ss.games, | |
| -- LAYER 1: the spine | |
| ss.spine_metric, ss.spine_opps, ss.team_spine_opps, ss.share_overall, | |
| -- LAYER 2: stress | |
| ss.spine_opps_stress, ss.team_spine_opps_stress, ss.share_stress, | |
| ss.spine_opps_calm, ss.team_spine_opps_calm, ss.share_calm, | |
| ss.confidence_delta, | |
| -- LAYER 3: depth chart | |
| d.depth_pos, d.depth_rank, d.depth_as_of_week, | |
| -- LAYER 4: where | |
| rz.opp_i20, rz.opp_i10, rz.opp_i5, rz.opp_g2g, | |
| rz.td_i20, rz.td_i5, | |
| rz.team_opp_i20, | |
| rz.pct_i20_intra, rz.pct_i20_shrunk, rz.pct_i10_shrunk, rz.pct_i5_shrunk, | |
| rz.pct_g2g_shrunk, | |
| -- LAYER 5: return | |
| sc.touches, sc.scrimmage_yards, sc.yards_per_touch, | |
| sc.scrimmage_yards_share_intra, sc.scrimmage_tds, | |
| -- intra-team usage ranks. The pos-group rank is the ONLY one comparable | |
| -- to depth_rank, since depth_rank is itself within-position (WR1/WR2). | |
| -- Ranking a TE against the team's WRs and calling the gap a surplus was | |
| -- a real bug caught on first run: every TE1 and RB1 showed a large | |
| -- negative surplus purely because receivers out-target them. | |
| row_number() OVER (PARTITION BY ss.season, ss.team ORDER BY ss.spine_opps DESC) | |
| AS usage_rank_on_team, | |
| row_number() OVER (PARTITION BY ss.season, ss.team, d.depth_pos | |
| ORDER BY ss.spine_opps DESC) AS usage_rank_in_pos_group | |
| FROM silver.v_nfl_stress_split ss | |
| LEFT JOIN silver.v_nfl_redzone_tiers rz | |
| ON rz.season = ss.season AND rz.team = ss.team AND rz.player_id = ss.player_id | |
| LEFT JOIN scrim sc | |
| ON sc.season = ss.season AND sc.team = ss.team AND sc.player_id = ss.player_id | |
| LEFT JOIN depth d | |
| ON d.season = ss.season AND d.team = ss.team AND d.player_id = ss.player_id | |
| LEFT JOIN silver.nfl_player p ON p.player_id = ss.player_id; | |
| -- ============================================================================= | |
| -- THE INDICATOR | |
| -- | |
| -- READ THIS BEFORE TRUSTING THE NUMBER. | |
| -- | |
| -- Everything above this line is an observed fact. This view is not. It is a | |
| -- weighted heuristic. It has NOT been backtested, and no claim is made that a | |
| -- high score predicts anything. Validating it -- does a rising score precede a | |
| -- prop line moving -- is Phase 2 and needs a walk-forward test on | |
| -- point-in-time data, not a correlation against this season's own totals. | |
| -- | |
| -- The weights sit in one CTE so they change in one place, and every input is | |
| -- exposed raw above so the score can always be taken apart. | |
| -- | |
| -- STRESS CARRIES THE MOST WEIGHT because it is the user's actual thesis: the | |
| -- tighter the situation, the more a coach goes to who he knows. Raw share is | |
| -- volume, which a book already prices. The stress delta is the part that says | |
| -- something a box score does not. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW gold.v_nfl_player_usage_index AS | |
| WITH weights AS ( | |
| -- Change here and nowhere else. Sums to 1.0. | |
| SELECT 0.34::DOUBLE AS w_stress, -- does his share GROW under pressure | |
| 0.22::DOUBLE AS w_redzone, -- share of the scarcest chances | |
| 0.20::DOUBLE AS w_share, -- baseline intra-team opportunity share | |
| 0.12::DOUBLE AS w_depth, -- out-earning his listed depth slot | |
| 0.12::DOUBLE AS w_return -- what happens once he has it | |
| ), base AS ( | |
| SELECT c.*, | |
| CASE WHEN c.depth_rank IS NULL THEN NULL | |
| ELSE c.depth_rank - c.usage_rank_in_pos_group END AS depth_surplus, | |
| -- Y/Tch scaled within his own team: "efficient" must mean efficient | |
| -- against the teammates competing for the same touches, not against | |
| -- the league, which would reintroduce the cross-team comparison | |
| -- this pond exists to avoid. | |
| CASE WHEN max(c.yards_per_touch) OVER w = min(c.yards_per_touch) OVER w THEN 0.5 | |
| ELSE (c.yards_per_touch - min(c.yards_per_touch) OVER w) | |
| / NULLIF(max(c.yards_per_touch) OVER w | |
| - min(c.yards_per_touch) OVER w, 0) END AS ypt_scaled_in_team, | |
| -- Shrink the stress delta by how many high-stress chances it rests | |
| -- on. A +40% delta off 2 opportunities is not a finding. At 8+ | |
| -- high-stress opportunities this is essentially unshrunk. | |
| COALESCE(c.confidence_delta, 0) | |
| * (COALESCE(c.spine_opps_stress, 0)::DOUBLE | |
| / (COALESCE(c.spine_opps_stress, 0) + 6)) AS confidence_delta_shrunk | |
| FROM gold.v_nfl_player_usage_components c | |
| WINDOW w AS (PARTITION BY c.season, c.team) | |
| ), scored AS ( | |
| SELECT b.*, | |
| -- stress axis: 0 delta -> 50. +0.20 (twenty points more share when | |
| -- tight) -> 100. Symmetric downward, so a coach visibly taking the | |
| -- ball OUT of someone's hands under pressure scores below 50. | |
| round(LEAST(GREATEST(50 + 250 * b.confidence_delta_shrunk, 0), 100), 1) | |
| AS stress_score, | |
| round(LEAST(100 * (0.5 * COALESCE(b.pct_i20_shrunk, 0) | |
| + 0.3 * COALESCE(b.pct_i10_shrunk, 0) | |
| + 0.2 * COALESCE(b.pct_i5_shrunk, 0)) / 0.32, 100), 1) | |
| AS redzone_score, | |
| round(LEAST(100 * COALESCE(b.share_overall, 0) / 0.35, 100), 1) | |
| AS share_score, | |
| round(50 + 50 * LEAST(GREATEST(COALESCE(b.depth_surplus, 0), -3), 3) / 3.0, 1) | |
| AS depth_score, | |
| round(LEAST(100 * (0.5 * COALESCE(b.ypt_scaled_in_team, 0) | |
| + 0.5 * COALESCE(b.scrimmage_yards_share_intra, 0) / 0.40), | |
| 100), 1) AS return_score | |
| FROM base b | |
| ) | |
| SELECT s.season, s.team, s.player_id, s.player_name, s.position, s.games, | |
| s.spine_metric, | |
| round(w.w_stress * s.stress_score | |
| + w.w_redzone * s.redzone_score | |
| + w.w_share * s.share_score | |
| + w.w_depth * s.depth_score | |
| + w.w_return * s.return_score, 1) AS usage_index_score, | |
| -- the axes, readable on their own | |
| s.stress_score, s.redzone_score, s.share_score, s.depth_score, s.return_score, | |
| -- LAYER 2 raw: the actual thesis, in plain numbers | |
| s.share_calm, s.share_stress, s.confidence_delta, | |
| s.spine_opps_stress, s.team_spine_opps_stress, | |
| -- the classification. A single score collapses cases a bettor must | |
| -- treat completely differently, so the role travels with it. | |
| CASE | |
| WHEN s.spine_opps < 5 THEN 'INSUFFICIENT SAMPLE' | |
| WHEN COALESCE(s.depth_surplus, 0) >= 1 | |
| AND s.share_score >= 20 THEN 'RISING -- OUT-EARNING DEPTH CHART' | |
| WHEN s.stress_score >= 60 AND s.share_score >= 40 THEN 'PRIMARY -- TRUSTED UNDER PRESSURE' | |
| WHEN s.stress_score >= 60 AND s.share_score < 40 THEN 'CLOSER -- SMALL ROLE, BIG MOMENTS' | |
| WHEN s.stress_score < 40 AND s.share_score >= 40 THEN 'VOLUME ONLY -- BENCHED WHEN IT TIGHTENS' | |
| WHEN s.redzone_score >= 45 AND s.share_score < 40 THEN 'RED ZONE SPECIALIST' | |
| WHEN s.share_score >= 20 THEN 'ROTATIONAL CONTRIBUTOR' | |
| ELSE 'FRINGE' | |
| END AS usage_role, | |
| -- facts carried through so the score is never taken on faith | |
| s.spine_opps, s.team_spine_opps, s.share_overall, | |
| s.usage_rank_on_team, s.usage_rank_in_pos_group, | |
| s.depth_pos, s.depth_rank, s.depth_surplus, s.depth_as_of_week, | |
| s.opp_i20, s.opp_i10, s.opp_i5, s.opp_g2g, s.team_opp_i20, | |
| s.pct_i20_intra, s.pct_i20_shrunk, s.td_i20, s.td_i5, | |
| s.touches, s.yards_per_touch, s.scrimmage_yards, | |
| s.scrimmage_yards_share_intra, s.scrimmage_tds | |
| FROM scored s CROSS JOIN weights w | |
| ORDER BY usage_index_score DESC; | |
| -- ============================================================================= | |
| -- "Who is THIS coach's son" -- one team at a time, ranked within itself. | |
| -- The pond's whole purpose. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW gold.v_nfl_player_usage_by_team AS | |
| SELECT team, season, | |
| row_number() OVER (PARTITION BY team, season ORDER BY usage_index_score DESC) | |
| AS rank_on_team, | |
| player_name, position, spine_metric, | |
| depth_pos, depth_rank, depth_surplus, | |
| usage_index_score, usage_role, | |
| stress_score, redzone_score, share_score, depth_score, return_score, | |
| spine_opps, team_spine_opps, share_overall, | |
| share_calm, share_stress, confidence_delta, | |
| opp_i20, team_opp_i20, yards_per_touch, scrimmage_yards | |
| FROM gold.v_nfl_player_usage_index | |
| ORDER BY team, rank_on_team; | |
| -- ============================================================================= | |
| -- CORRELATION: usage index x the TD log | |
| -- | |
| -- The user's framing, and the reason the TD logs were built before this pond: | |
| -- | |
| -- The TD log exists upstream of this pond for exactly this join: it is the | |
| -- confirmation step for the usage read. Related stats must agree -- if the | |
| -- usage index is right, the scoring log should echo it. | |
| -- | |
| -- The usage index says who the coach LEANS ON. | |
| -- silver.nfl_td_log says who actually FINISHED. Putting them side by side turns | |
| -- an opinion into a testable claim: | |
| -- | |
| -- agreement -- high confidence AND scoring. The read holds. | |
| -- opportunity -- high confidence, no TDs yet. The coach keeps going back to | |
| -- him and it has not cashed. Anytime-TD markets price the | |
| -- result, not the usage, so this is where a line lags. | |
| -- hollow -- TDs without confidence. Scored on volume or a broken play, | |
| -- not on trust. The most dangerous profile to bet on repeating, | |
| -- which is exactly the profile a book will advertise. | |
| -- | |
| -- Both sides are already in this lake and both are facts. The only judgement | |
| -- here is the label, and the counts that produce it travel with it. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW gold.v_nfl_player_usage_td_correlation AS | |
| WITH td AS ( | |
| SELECT season, scorer_player_id AS player_id, | |
| ANY_VALUE(scoring_team) AS team, | |
| count(*) AS tds, | |
| count(*) FILTER (WHERE is_red_zone) AS tds_red_zone, | |
| count(*) FILTER (WHERE is_goal_to_go) AS tds_goal_to_go, | |
| count(*) FILTER (WHERE is_third_down) AS tds_third_down, | |
| count(*) FILTER (WHERE td_type = 'rush') AS tds_rush, | |
| count(*) FILTER (WHERE td_type = 'pass') AS tds_rec, | |
| count(DISTINCT game_id) AS games_with_td | |
| FROM silver.nfl_td_log | |
| WHERE scorer_player_id IS NOT NULL | |
| GROUP BY season, scorer_player_id | |
| ) | |
| SELECT cs.season, cs.team, cs.player_id, cs.player_name, cs.position, | |
| cs.spine_metric, cs.usage_index_score, cs.usage_role, | |
| -- the confidence side | |
| cs.share_overall, cs.share_calm, cs.share_stress, cs.confidence_delta, | |
| cs.opp_i20, cs.opp_i10, cs.opp_i5, cs.opp_g2g, | |
| cs.pct_i20_shrunk, | |
| -- the finishing side (TD log) | |
| COALESCE(td.tds, 0) AS tds, | |
| COALESCE(td.tds_red_zone, 0) AS tds_red_zone, | |
| COALESCE(td.tds_goal_to_go, 0) AS tds_goal_to_go, | |
| COALESCE(td.tds_rush, 0) AS tds_rush, | |
| COALESCE(td.tds_rec, 0) AS tds_rec, | |
| COALESCE(td.games_with_td, 0) AS games_with_td, | |
| -- conversion: TDs per red-zone opportunity. The bridge between the two. | |
| round(1.0 * COALESCE(td.tds_red_zone, 0) / NULLIF(cs.opp_i20, 0), 3) | |
| AS td_per_redzone_opp, | |
| -- IF THIS THEN THAT: does the confidence read agree with the scoring? | |
| CASE | |
| WHEN cs.usage_index_score >= 55 AND COALESCE(td.tds, 0) >= 2 | |
| THEN 'AGREEMENT -- trusted and finishing' | |
| WHEN cs.usage_index_score >= 55 AND COALESCE(td.tds, 0) = 0 | |
| THEN 'OPPORTUNITY -- trusted, has not cashed yet' | |
| WHEN cs.usage_index_score >= 55 | |
| THEN 'AGREEMENT -- trusted, finishing lightly' | |
| WHEN cs.usage_index_score < 40 AND COALESCE(td.tds, 0) >= 2 | |
| THEN 'HOLLOW -- scoring without trust, fragile' | |
| WHEN COALESCE(td.tds, 0) = 0 AND cs.usage_index_score < 40 | |
| THEN 'QUIET -- neither trusted nor scoring' | |
| ELSE 'MIXED' | |
| END AS correlation_flag | |
| FROM gold.v_nfl_player_usage_index cs | |
| LEFT JOIN td ON td.season = cs.season AND td.player_id = cs.player_id | |
| WHERE cs.usage_role <> 'INSUFFICIENT SAMPLE' | |
| ORDER BY cs.usage_index_score DESC; | |