Spaces:
Paused
Paused
| -- ============================================================================= | |
| -- frontal-lobe2 : NFL schedule -- the backbone the Match Page is built on | |
| -- ============================================================================= | |
| -- Fixes a confirmed real gap: silver.nfl_game (sql/nfl_pfr_schema.sql) has | |
| -- home_team/away_team/kickoff_utc as NULL for every row -- those columns were | |
| -- defined but the loader never had a source that actually populated them. | |
| -- This is that source. | |
| -- | |
| -- Source: nflverse-data 'schedules' release (games.csv), confirmed live with | |
| -- real 2026 data 2026-09-25. 272 games for the 2026 season. | |
| -- | |
| -- Confirmed field behavior (not assumed): | |
| -- - gameday/gametime: populated for ALL games, past and future. | |
| -- - spread_line/total_line/home_moneyline/away_moneyline: populated for | |
| -- BOTH past AND upcoming games (e.g. 2026_03_LAC_BUF, played 2026-09-27, | |
| -- already carries spread=7, total=50.5 as of 2026-09-25). This is a | |
| -- second, independent game-level odds source alongside RotoWire's | |
| -- player-prop lines -- different grain (team spread/total vs. player | |
| -- prop), kept in separate tables, not merged. | |
| -- - temp/wind/roof/surface: temp and wind are ONLY populated for games | |
| -- that have already been played (recorded actual conditions), NOT a | |
| -- forward-looking forecast. Confirmed: only 20/272 2026 games have a | |
| -- temp value, and all upcoming games show blank. roof/surface (dome vs | |
| -- outdoors, turf vs grass) ARE known ahead of time and populated for | |
| -- upcoming games too -- that's a fixed stadium property, not a forecast. | |
| -- - A real weather FORECAST for upcoming games would need a different, | |
| -- forward-looking source -- not built here. See docs/MATCH_PAGE.md. | |
| -- | |
| -- Engine: DuckDB. | |
| -- ============================================================================= | |
| CREATE SCHEMA IF NOT EXISTS silver; | |
| CREATE TABLE IF NOT EXISTS silver.nfl_schedule ( | |
| game_id VARCHAR PRIMARY KEY, -- matches game_id used elsewhere (nfl_td_log, player-week tables) | |
| season INTEGER NOT NULL, | |
| game_type VARCHAR, -- REG | WC | DIV | CON | SB etc. | |
| week INTEGER NOT NULL, | |
| game_date DATE NOT NULL, -- gameday | |
| weekday VARCHAR, | |
| game_time_local VARCHAR, -- gametime, as published (stadium-local, not normalized to UTC -- see docs) | |
| away_team VARCHAR NOT NULL, | |
| home_team VARCHAR NOT NULL, | |
| away_score INTEGER, -- NULL until played | |
| home_score INTEGER, | |
| location VARCHAR, -- 'Home' | 'Neutral' | |
| result_margin INTEGER, -- home - away, once played | |
| total_points INTEGER, | |
| overtime BOOLEAN, | |
| -- fixed stadium properties, known ahead of time (not a forecast) | |
| roof VARCHAR, -- outdoors | dome | closed | open | |
| surface VARCHAR, -- grass | turf variants | |
| stadium_id VARCHAR, | |
| stadium VARCHAR, | |
| -- recorded actual conditions -- ONLY populated after the game is played | |
| temp_actual INTEGER, | |
| wind_actual INTEGER, | |
| -- game-level odds (spread/total/moneyline). Populated for upcoming games | |
| -- too, unlike temp/wind. Historical rows show the CLOSING line, not | |
| -- necessarily what a book showed pre-game -- treat as a reference line, | |
| -- not a live-updating snapshot (RotoWire's prop lines ARE snapshotted | |
| -- for movement; this schedule feed is not). | |
| spread_line DOUBLE, -- home team spread (negative = home favored) | |
| away_spread_odds INTEGER, | |
| home_spread_odds INTEGER, | |
| total_line DOUBLE, | |
| over_odds INTEGER, | |
| under_odds INTEGER, | |
| away_moneyline INTEGER, | |
| home_moneyline INTEGER, | |
| div_game BOOLEAN, | |
| away_qb_name VARCHAR, | |
| home_qb_name VARCHAR, | |
| away_coach VARCHAR, | |
| home_coach VARCHAR, | |
| referee VARCHAR, | |
| ingest_run_id VARCHAR NOT NULL | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_schedule_date ON silver.nfl_schedule (game_date); | |
| CREATE INDEX IF NOT EXISTS ix_schedule_season_week ON silver.nfl_schedule (season, week); | |
| CREATE INDEX IF NOT EXISTS ix_schedule_teams ON silver.nfl_schedule (home_team, away_team); | |
| -- ============================================================================= | |
| -- ATS (against the spread) -- computed here, not sourced elsewhere. | |
| -- Checked nflverse's own release catalog (stats_player, stats_team, pbp, | |
| -- injuries, schedules, pfr_advstats, snap_counts, depth_charts): none of them | |
| -- publish a rolling covers-percentage stat. That's not a gap in what we | |
| -- pulled -- nflverse simply doesn't track that as a derived metric. But we | |
| -- don't need a new source for it: this table already has real closing | |
| -- spreads + real final scores for every played game, which is 100% of what | |
| -- ATS math needs. "Last 5 covers %" is a rolling window over data we already | |
| -- have, not a missing-source problem. | |
| -- ============================================================================= | |
| -- One row per team per game they played, spread-adjusted result. Both home | |
| -- and away perspectives are unioned so every team has one row per game. | |
| CREATE OR REPLACE VIEW silver.v_nfl_team_ats_game AS | |
| SELECT game_id, season, week, game_date, home_team AS team, away_team AS opponent, | |
| true AS was_home, -spread_line AS spread_needed, result_margin AS actual_margin, | |
| CASE WHEN result_margin > -spread_line THEN 'cover' | |
| WHEN result_margin < -spread_line THEN 'no_cover' ELSE 'push' END AS ats_result | |
| FROM silver.nfl_schedule | |
| WHERE home_score IS NOT NULL AND spread_line IS NOT NULL | |
| UNION ALL | |
| SELECT game_id, season, week, game_date, away_team, home_team, | |
| false, spread_line, -result_margin, | |
| CASE WHEN -result_margin > spread_line THEN 'cover' | |
| WHEN -result_margin < spread_line THEN 'no_cover' ELSE 'push' END | |
| FROM silver.nfl_schedule | |
| WHERE home_score IS NOT NULL AND spread_line IS NOT NULL; | |
| -- Season-to-date ATS record + rolling last-5 and last-10 windows per team. | |
| -- Uses DuckDB window functions ordered by game_date so "last 5" always means | |
| -- the 5 most recent played games for that team, regardless of week gaps | |
| -- (byes) -- not a fixed week range. | |
| CREATE OR REPLACE VIEW silver.v_nfl_team_ats_rolling AS | |
| SELECT game_id, season, week, game_date, team, opponent, was_home, | |
| spread_needed, actual_margin, ats_result, | |
| row_number() OVER (PARTITION BY team, season ORDER BY game_date) AS game_num, | |
| sum(CASE WHEN ats_result = 'cover' THEN 1 ELSE 0 END) | |
| OVER (PARTITION BY team, season ORDER BY game_date | |
| ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS covers_last_5, | |
| count(*) FILTER (WHERE ats_result != 'push') | |
| OVER (PARTITION BY team, season ORDER BY game_date | |
| ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS graded_games_last_5, | |
| sum(CASE WHEN ats_result = 'cover' THEN 1 ELSE 0 END) | |
| OVER (PARTITION BY team, season ORDER BY game_date | |
| ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS covers_last_10, | |
| count(*) FILTER (WHERE ats_result != 'push') | |
| OVER (PARTITION BY team, season ORDER BY game_date | |
| ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS graded_games_last_10 | |
| FROM silver.v_nfl_team_ats_game; | |
| -- The actual "who covers, who doesn't" leaderboard, as of the most recent | |
| -- game each team has played. Percentage, not just raw counts. | |
| CREATE OR REPLACE VIEW silver.v_nfl_team_ats_current AS | |
| SELECT team, | |
| max(game_num) AS games_played_this_season, | |
| max_by(covers_last_5, game_num) AS covers_last_5, | |
| max_by(graded_games_last_5, game_num) AS games_last_5, | |
| round(100.0 * max_by(covers_last_5, game_num) | |
| / NULLIF(max_by(graded_games_last_5, game_num), 0), 1) AS cover_pct_last_5, | |
| max_by(covers_last_10, game_num) AS covers_last_10, | |
| max_by(graded_games_last_10, game_num) AS games_last_10, | |
| round(100.0 * max_by(covers_last_10, game_num) | |
| / NULLIF(max_by(graded_games_last_10, game_num), 0), 1) AS cover_pct_last_10 | |
| FROM silver.v_nfl_team_ats_rolling | |
| GROUP BY team | |
| ORDER BY cover_pct_last_5 DESC NULLS LAST; | |