-- ============================================================================= -- frontal-lobe2 : NFL "Pro-Football-Reference style" schema section -- ============================================================================= -- Recreates the chart/table design of the PFR 2026 season pages: -- passing.htm | rushing.htm | receiving.htm | scrimmage.htm -- defense.htm | scoring.htm | kicking.htm -- for BOTH players and teams. -- -- SCOPE: 2026 season only (current season). Season is still a column everywhere so -- scaling to more seasons later is a data change, not a schema change. -- -- GRAIN: one row per player (or team) PER WEEK PER GAME. This is deliberate -- -- season totals are a derived view (see bottom), not the base table. Prop markets -- are priced per game, so week grain is required to go head-to-head with a book's -- line on a specific matchup. -- -- SOURCE OF COLUMNS: verified against real nflverse 2026 files on 2026-09-24 -- (weeks 1-2 present, REG season). NOT scraped from PFR -- PFR sits behind a -- Cloudflare bot challenge (HTTP 403 on every path incl. robots.txt) and its ToS -- prohibits harvesting. nflverse republishes the same underlying stats as open -- data, including a direct mirror of PFR advanced stats. See docs/PFR_SOURCE_NOTE.md. -- -- Engine: DuckDB. -- ============================================================================= CREATE SCHEMA IF NOT EXISTS bronze; CREATE SCHEMA IF NOT EXISTS silver; CREATE SCHEMA IF NOT EXISTS gold; -- ============================================================================= -- BRONZE: ingestion audit. One row per loader run so any silver row can be traced -- back to the exact pull that produced it (ingest_run_id on every fact table). -- ============================================================================= CREATE TABLE IF NOT EXISTS bronze.ingestion_runs ( run_id VARCHAR PRIMARY KEY, source_name VARCHAR NOT NULL, -- 'nflverse' | 'nflverse_pbp' source_kind VARCHAR NOT NULL, -- 'api' | 'html_snapshot' league VARCHAR, started_at TIMESTAMP NOT NULL, completed_at TIMESTAMP, row_count INTEGER, status VARCHAR NOT NULL, -- success | failed error_message VARCHAR ); -- ============================================================================= -- SHARED DIMENSIONS -- ============================================================================= -- Player identity. nflverse gpsis/player_id is the join key; pfr_player_id lets us -- reconcile against anything PFR-derived (advanced stats arrive keyed that way). CREATE TABLE IF NOT EXISTS silver.nfl_player ( player_id VARCHAR PRIMARY KEY, -- nflverse player_id pfr_player_id VARCHAR, -- PFR id, from advstats feeds player_name VARCHAR, -- short/abbrev form player_display_name VARCHAR NOT NULL, position VARCHAR, position_group VARCHAR, headshot_url VARCHAR ); CREATE INDEX IF NOT EXISTS ix_nfl_player_pfr ON silver.nfl_player (pfr_player_id); -- Game spine. Every stat row hangs off (season, week, game_id). CREATE TABLE IF NOT EXISTS silver.nfl_game ( game_id VARCHAR PRIMARY KEY, -- nflverse game_id pfr_game_id VARCHAR, season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, -- REG | POST home_team VARCHAR, away_team VARCHAR, kickoff_utc TIMESTAMP ); CREATE INDEX IF NOT EXISTS ix_nfl_game_sw ON silver.nfl_game (season, week); -- ============================================================================= -- PLAYER TABLES -- one per PFR page, matching that page's chart -- ============================================================================= -- PFR passing.htm (#all_passing) CREATE TABLE IF NOT EXISTS silver.nfl_player_passing_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, completions INTEGER, attempts INTEGER, passing_yards INTEGER, passing_tds INTEGER, passing_interceptions INTEGER, sacks_suffered DOUBLE, sack_yards_lost INTEGER, sack_fumbles INTEGER, sack_fumbles_lost INTEGER, passing_air_yards INTEGER, passing_yards_after_catch DOUBLE, passing_first_downs INTEGER, passing_epa DOUBLE, passing_cpoe DOUBLE, passing_2pt_conversions INTEGER, pacr DOUBLE, -- passing air conversion ratio passing_10 INTEGER, -- completions of 10+ air yds passing_16 INTEGER, passing_20 INTEGER, passing_40 INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR rushing.htm (#all_rushing) CREATE TABLE IF NOT EXISTS silver.nfl_player_rushing_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, carries INTEGER, rushing_yards INTEGER, rushing_tds INTEGER, rushing_fumbles INTEGER, rushing_fumbles_lost INTEGER, rushing_first_downs INTEGER, rushing_epa DOUBLE, rushing_2pt_conversions INTEGER, rushing_10 INTEGER, -- rushes of 10+ yds rushing_12 INTEGER, rushing_20 INTEGER, rushing_40 INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR receiving.htm (#all_receiving) CREATE TABLE IF NOT EXISTS silver.nfl_player_receiving_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, receptions INTEGER, targets INTEGER, receiving_yards INTEGER, receiving_tds INTEGER, receiving_fumbles INTEGER, receiving_fumbles_lost INTEGER, receiving_air_yards INTEGER, receiving_yards_after_catch DOUBLE, receiving_first_downs INTEGER, receiving_epa DOUBLE, receiving_2pt_conversions INTEGER, receiving_10 INTEGER, receiving_16 INTEGER, receiving_20 INTEGER, receiving_40 INTEGER, racr DOUBLE, -- receiver air conversion ratio target_share DOUBLE, air_yards_share DOUBLE, wopr DOUBLE, -- weighted opportunity rating ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR scrimmage.htm (#all_scrimmage) -- rushing + receiving combined ("all-purpose"). -- Kept as its own table because PFR presents it as its own chart with derived totals. CREATE TABLE IF NOT EXISTS silver.nfl_player_scrimmage_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, touches INTEGER, -- carries + receptions scrimmage_yards INTEGER, -- rushing_yards + receiving_yards scrimmage_tds INTEGER, -- rushing_tds + receiving_tds yards_per_touch DOUBLE, scrimmage_first_downs INTEGER, scrimmage_fumbles INTEGER, carries INTEGER, rushing_yards INTEGER, rushing_tds INTEGER, receptions INTEGER, targets INTEGER, receiving_yards INTEGER, receiving_tds INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR defense.htm (#all_defense) CREATE TABLE IF NOT EXISTS silver.nfl_player_defense_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, def_tackles_solo INTEGER, def_tackles_with_assist INTEGER, def_tackle_assists INTEGER, def_tackles_for_loss INTEGER, def_tackles_for_loss_yards DOUBLE, def_fumbles_forced INTEGER, def_sacks DOUBLE, def_sack_yards DOUBLE, def_qb_hits INTEGER, def_interceptions INTEGER, def_interception_yards INTEGER, def_pass_defended INTEGER, def_tds INTEGER, def_fumbles INTEGER, def_safeties INTEGER, def_punt_blocks INTEGER, def_pat_blocks INTEGER, def_fg_blocks INTEGER, def_2pt_atts INTEGER, def_2pt_made INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR scoring.htm (#all_scoring) -- every way a player put points on the board. CREATE TABLE IF NOT EXISTS silver.nfl_player_scoring_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, passing_tds INTEGER, rushing_tds INTEGER, receiving_tds INTEGER, def_tds INTEGER, special_teams_tds INTEGER, fumble_recovery_tds INTEGER, pt_return_tds INTEGER, -- punt-team return TDs total_tds INTEGER, passing_2pt_conversions INTEGER, rushing_2pt_conversions INTEGER, receiving_2pt_conversions INTEGER, def_2pt_made INTEGER, pat_made INTEGER, fg_made INTEGER, def_safeties INTEGER, total_points INTEGER, -- 6*TD + 2*2pt + 1*PAT + 3*FG + 2*safety fantasy_points DOUBLE, fantasy_points_ppr DOUBLE, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR kicking.htm (#all_kicking) -- FG + PAT + punting, incl. PFR's distance buckets. CREATE TABLE IF NOT EXISTS silver.nfl_player_kicking_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, -- field goals fg_made INTEGER, fg_att INTEGER, fg_missed INTEGER, fg_blocked INTEGER, fg_long INTEGER, fg_pct DOUBLE, fg_made_0_19 INTEGER, fg_made_20_29 INTEGER, fg_made_30_39 INTEGER, fg_made_40_49 INTEGER, fg_made_50_59 INTEGER, fg_made_60_ INTEGER, fg_missed_0_19 INTEGER, fg_missed_20_29 INTEGER, fg_missed_30_39 INTEGER, fg_missed_40_49 INTEGER, fg_missed_50_59 INTEGER, fg_missed_60_ INTEGER, fg_made_distance DOUBLE, fg_missed_distance DOUBLE, fg_blocked_distance DOUBLE, -- game-winning FG gwfg_made INTEGER, gwfg_att INTEGER, gwfg_missed INTEGER, gwfg_blocked INTEGER, gwfg_distance DOUBLE, -- extra points pat_made INTEGER, pat_att INTEGER, pat_missed INTEGER, pat_blocked INTEGER, pat_pct DOUBLE, -- punting pt_att INTEGER, pt_yards INTEGER, pt_net_yards INTEGER, pt_long INTEGER, pt_blocked INTEGER, pt_inside_20 INTEGER, pt_out_of_bounds INTEGER, pt_downed INTEGER, pt_touchback INTEGER, pt_fair_caught INTEGER, pt_returned INTEGER, pt_return_yards INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- PFR returns (punt/kick return) -- shares the kicking/special-teams family. CREATE TABLE IF NOT EXISTS silver.nfl_player_returns_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, player_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, punt_returns INTEGER, punt_return_yards INTEGER, kickoff_returns INTEGER, kickoff_return_yards INTEGER, special_teams_tds INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, player_id) ); -- ============================================================================= -- PFR ADVANCED STATS -- nflverse mirrors PFR's own advanced tables directly. -- Keyed on pfr_player_id (that's how the source arrives); join via silver.nfl_player. -- These are the columns a book's model may not be pricing well -- pressure rates, -- yards after contact, broken tackles, coverage stats. -- ============================================================================= CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_passing_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, game_type VARCHAR, game_id VARCHAR, pfr_game_id VARCHAR, pfr_player_id VARCHAR NOT NULL, pfr_player_name VARCHAR, team VARCHAR NOT NULL, opponent VARCHAR, passing_drops INTEGER, passing_drop_pct DOUBLE, passing_bad_throws INTEGER, passing_bad_throw_pct DOUBLE, times_sacked INTEGER, times_blitzed INTEGER, times_hurried INTEGER, times_hit INTEGER, times_pressured INTEGER, times_pressured_pct DOUBLE, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, pfr_player_id, team) ); CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_rushing_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, game_type VARCHAR, game_id VARCHAR, pfr_game_id VARCHAR, pfr_player_id VARCHAR NOT NULL, pfr_player_name VARCHAR, team VARCHAR NOT NULL, opponent VARCHAR, carries INTEGER, rushing_yards_before_contact DOUBLE, rushing_yards_before_contact_avg DOUBLE, rushing_yards_after_contact DOUBLE, rushing_yards_after_contact_avg DOUBLE, rushing_broken_tackles INTEGER, receiving_broken_tackles INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, pfr_player_id, team) ); CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_receiving_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, game_type VARCHAR, game_id VARCHAR, pfr_game_id VARCHAR, pfr_player_id VARCHAR NOT NULL, pfr_player_name VARCHAR, team VARCHAR NOT NULL, opponent VARCHAR, rushing_broken_tackles INTEGER, receiving_broken_tackles INTEGER, receiving_drop INTEGER, receiving_drop_pct DOUBLE, receiving_int INTEGER, receiving_rat DOUBLE, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, pfr_player_id, team) ); CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_defense_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, game_type VARCHAR, game_id VARCHAR, pfr_game_id VARCHAR, pfr_player_id VARCHAR NOT NULL, pfr_player_name VARCHAR, team VARCHAR NOT NULL, opponent VARCHAR, def_ints INTEGER, def_targets INTEGER, def_completions_allowed INTEGER, def_completion_pct DOUBLE, def_yards_allowed INTEGER, def_yards_allowed_per_cmp DOUBLE, def_yards_allowed_per_tgt DOUBLE, def_receiving_td_allowed INTEGER, def_passer_rating_allowed DOUBLE, def_adot DOUBLE, -- avg depth of target def_air_yards_completed INTEGER, def_yards_after_catch DOUBLE, def_times_blitzed INTEGER, def_times_hurried INTEGER, def_times_hitqb INTEGER, def_sacks DOUBLE, def_pressures INTEGER, def_tackles_combined INTEGER, def_missed_tackles INTEGER, def_missed_tackle_pct DOUBLE, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, pfr_player_id, team) ); -- ============================================================================= -- TEAM TABLES -- same 7 chart families, team grain. -- One wide weekly table mirrors the source exactly; per-chart views slice it so -- each PFR page still has a 1:1 counterpart without duplicating storage. -- ============================================================================= CREATE TABLE IF NOT EXISTS silver.nfl_team_week ( season INTEGER NOT NULL, week INTEGER NOT NULL, season_type VARCHAR NOT NULL, game_id VARCHAR NOT NULL, team VARCHAR NOT NULL, opponent_team VARCHAR, -- passing completions INTEGER, attempts INTEGER, passing_yards INTEGER, passing_tds INTEGER, passing_interceptions INTEGER, sacks_suffered DOUBLE, sack_yards_lost INTEGER, passing_air_yards INTEGER, passing_yards_after_catch DOUBLE, passing_first_downs INTEGER, passing_epa DOUBLE, passing_cpoe DOUBLE, -- rushing carries INTEGER, rushing_yards INTEGER, rushing_tds INTEGER, rushing_first_downs INTEGER, rushing_epa DOUBLE, -- receiving receptions INTEGER, targets INTEGER, receiving_yards INTEGER, receiving_tds INTEGER, receiving_air_yards INTEGER, receiving_yards_after_catch DOUBLE, receiving_first_downs INTEGER, receiving_epa DOUBLE, -- defense def_tackles_solo INTEGER, def_sacks DOUBLE, def_sack_yards DOUBLE, def_qb_hits INTEGER, def_interceptions INTEGER, def_pass_defended INTEGER, def_tds INTEGER, def_safeties INTEGER, def_fumbles_forced INTEGER, def_tackles_for_loss INTEGER, -- scoring / special special_teams_tds INTEGER, fumble_recovery_tds INTEGER, fg_made INTEGER, fg_att INTEGER, fg_long INTEGER, fg_pct DOUBLE, pat_made INTEGER, pat_att INTEGER, pat_pct DOUBLE, -- punting / returns pt_att INTEGER, pt_yards INTEGER, pt_net_yards INTEGER, pt_inside_20 INTEGER, punt_returns INTEGER, punt_return_yards INTEGER, kickoff_returns INTEGER, kickoff_return_yards INTEGER, -- discipline / ball security penalties INTEGER, penalty_yards INTEGER, fumbles_total INTEGER, fumbles_lost_total INTEGER, timeouts INTEGER, ingest_run_id VARCHAR NOT NULL, PRIMARY KEY (season, week, game_id, team) ); -- Per-PFR-page team views (1:1 with the site's charts, no extra storage) CREATE OR REPLACE VIEW silver.v_nfl_team_passing_week AS SELECT season, week, season_type, game_id, team, opponent_team, completions, attempts, passing_yards, passing_tds, passing_interceptions, sacks_suffered, sack_yards_lost, passing_air_yards, passing_yards_after_catch, passing_first_downs, passing_epa, passing_cpoe FROM silver.nfl_team_week; CREATE OR REPLACE VIEW silver.v_nfl_team_rushing_week AS SELECT season, week, season_type, game_id, team, opponent_team, carries, rushing_yards, rushing_tds, rushing_first_downs, rushing_epa FROM silver.nfl_team_week; CREATE OR REPLACE VIEW silver.v_nfl_team_receiving_week AS SELECT season, week, season_type, game_id, team, opponent_team, receptions, targets, receiving_yards, receiving_tds, receiving_air_yards, receiving_yards_after_catch, receiving_first_downs, receiving_epa FROM silver.nfl_team_week; CREATE OR REPLACE VIEW silver.v_nfl_team_scrimmage_week AS SELECT season, week, season_type, game_id, team, opponent_team, carries + receptions AS touches, rushing_yards + receiving_yards AS scrimmage_yards, rushing_tds + receiving_tds AS scrimmage_tds, rushing_first_downs + receiving_first_downs AS scrimmage_first_downs FROM silver.nfl_team_week; CREATE OR REPLACE VIEW silver.v_nfl_team_defense_week AS SELECT season, week, season_type, game_id, team, opponent_team, def_tackles_solo, def_tackles_for_loss, def_sacks, def_sack_yards, def_qb_hits, def_interceptions, def_pass_defended, def_tds, def_safeties, def_fumbles_forced FROM silver.nfl_team_week; CREATE OR REPLACE VIEW silver.v_nfl_team_scoring_week AS SELECT season, week, season_type, game_id, team, opponent_team, passing_tds, rushing_tds, receiving_tds, def_tds, special_teams_tds, fumble_recovery_tds, COALESCE(rushing_tds,0) + COALESCE(receiving_tds,0) + COALESCE(def_tds,0) + COALESCE(special_teams_tds,0) AS total_tds, fg_made, pat_made, def_safeties, 6 * (COALESCE(rushing_tds,0) + COALESCE(receiving_tds,0) + COALESCE(def_tds,0) + COALESCE(special_teams_tds,0)) + 3 * COALESCE(fg_made,0) + COALESCE(pat_made,0) + 2 * COALESCE(def_safeties,0) AS points_scored FROM silver.nfl_team_week; CREATE OR REPLACE VIEW silver.v_nfl_team_kicking_week AS SELECT season, week, season_type, game_id, team, opponent_team, fg_made, fg_att, fg_long, fg_pct, pat_made, pat_att, pat_pct, pt_att, pt_yards, pt_net_yards, pt_inside_20 FROM silver.nfl_team_week; -- ============================================================================= -- SEASON AGGREGATE VIEWS -- what PFR's season page shows. Derived, never stored, -- so a late stat correction upstream can't leave totals out of sync with weeks. -- ============================================================================= CREATE OR REPLACE VIEW silver.v_nfl_player_passing_season AS SELECT season, season_type, player_id, ANY_VALUE(team) AS team, COUNT(*) AS games, SUM(completions) AS completions, SUM(attempts) AS attempts, SUM(passing_yards) AS passing_yards, SUM(passing_tds) AS passing_tds, SUM(passing_interceptions) AS passing_interceptions, SUM(passing_first_downs) AS passing_first_downs, SUM(sacks_suffered) AS sacks_suffered, CASE WHEN SUM(attempts) > 0 THEN 1.0 * SUM(completions) / SUM(attempts) END AS completion_pct, CASE WHEN SUM(attempts) > 0 THEN 1.0 * SUM(passing_yards) / SUM(attempts) END AS yards_per_attempt, SUM(passing_epa) AS passing_epa FROM silver.nfl_player_passing_week GROUP BY season, season_type, player_id; CREATE OR REPLACE VIEW silver.v_nfl_player_rushing_season AS SELECT season, season_type, player_id, ANY_VALUE(team) AS team, COUNT(*) AS games, SUM(carries) AS carries, SUM(rushing_yards) AS rushing_yards, SUM(rushing_tds) AS rushing_tds, SUM(rushing_first_downs) AS rushing_first_downs, CASE WHEN SUM(carries) > 0 THEN 1.0 * SUM(rushing_yards) / SUM(carries) END AS yards_per_carry, SUM(rushing_epa) AS rushing_epa FROM silver.nfl_player_rushing_week GROUP BY season, season_type, player_id; CREATE OR REPLACE VIEW silver.v_nfl_player_receiving_season AS SELECT season, season_type, player_id, ANY_VALUE(team) AS team, COUNT(*) AS games, SUM(receptions) AS receptions, SUM(targets) AS targets, SUM(receiving_yards) AS receiving_yards, SUM(receiving_tds) AS receiving_tds, SUM(receiving_first_downs) AS receiving_first_downs, CASE WHEN SUM(targets) > 0 THEN 1.0 * SUM(receptions) / SUM(targets) END AS catch_rate, CASE WHEN SUM(receptions) > 0 THEN 1.0 * SUM(receiving_yards) / SUM(receptions) END AS yards_per_reception, AVG(target_share) AS avg_target_share, SUM(receiving_epa) AS receiving_epa FROM silver.nfl_player_receiving_week GROUP BY season, season_type, player_id; -- ============================================================================= -- GOLD: the head-to-head layer. Player form vs. a book's posted prop line. -- -- SUPERSEDED (confirmed empty, 0 rows, 2026-09-25): this was the original -- placeholder before a real props source was scoped. gold.nfl_prop_line_rotowire -- (sql/nfl_props_schema.sql) is the real, populated table -- 4,956+ rows, -- verified real DK/FD/Caesars lines. Kept here (not dropped) so nothing that -- may already reference this name breaks; do not write new code against it. -- ============================================================================= CREATE TABLE IF NOT EXISTS gold.nfl_prop_line ( league VARCHAR NOT NULL DEFAULT 'nfl', season INTEGER NOT NULL, week INTEGER NOT NULL, game_id VARCHAR, player_id VARCHAR, book VARCHAR NOT NULL, market VARCHAR NOT NULL, -- e.g. player_pass_yds, player_rush_yds line DOUBLE, over_price_decimal DOUBLE, under_price_decimal DOUBLE, fetched_at_utc TIMESTAMP NOT NULL, source_name VARCHAR NOT NULL, -- 'the_odds_api' PRIMARY KEY (season, week, book, market, player_id, fetched_at_utc) ); -- Rolling player form, point-in-time safe: only weeks strictly BEFORE the target -- week feed the average, so a backtest can't peek at the result it's predicting. CREATE OR REPLACE VIEW gold.v_nfl_player_form_passing AS SELECT p.season, p.week AS as_of_week, p.player_id, AVG(h.passing_yards) OVER w AS avg_passing_yards_prior, AVG(h.attempts) OVER w AS avg_attempts_prior, COUNT(*) OVER w AS games_in_window FROM silver.nfl_player_passing_week p JOIN silver.nfl_player_passing_week h ON h.player_id = p.player_id AND h.season = p.season AND h.week < p.week WINDOW w AS (PARTITION BY p.player_id, p.season, p.week); CREATE INDEX IF NOT EXISTS ix_pass_wk ON silver.nfl_player_passing_week (season, week, player_id); CREATE INDEX IF NOT EXISTS ix_rush_wk ON silver.nfl_player_rushing_week (season, week, player_id); CREATE INDEX IF NOT EXISTS ix_rec_wk ON silver.nfl_player_receiving_week (season, week, player_id); CREATE INDEX IF NOT EXISTS ix_def_wk ON silver.nfl_player_defense_week (season, week, player_id); CREATE INDEX IF NOT EXISTS ix_kick_wk ON silver.nfl_player_kicking_week (season, week, player_id); CREATE INDEX IF NOT EXISTS ix_team_wk ON silver.nfl_team_week (season, week, team); CREATE INDEX IF NOT EXISTS ix_propline ON gold.nfl_prop_line (season, week, market, player_id);