Spaces:
Paused
Paused
| -- ============================================================================= | |
| -- frontal-lobe2 : NFL touchdown scoring logs (all 32 teams, no manual paste) | |
| -- ============================================================================= | |
| -- Recreates the two PFR team-page charts: | |
| -- /teams/<abbr>/2026.htm#all_team_td_log -> team touchdowns scored | |
| -- /teams/<abbr>/2026.htm#all_opp_td_log -> touchdowns allowed to opponents | |
| -- | |
| -- KEY INSIGHT: those two PFR charts are two views of the SAME touchdown event -- | |
| -- one from the scoring team's side, one from the conceding defense's side. So we | |
| -- store one row per touchdown play and expose two views. No duplicated storage, | |
| -- and no scraping 32 separate team pages. | |
| -- | |
| -- SOURCE: nflverse play_by_play_<season> (open data). Every touchdown play carries | |
| -- td_team, td_player_id/name, quarter, clock, yardline_100, down, goal_to_go and | |
| -- pass/rush/return flags -- so the log is reconstructed exactly, with MORE detail | |
| -- than PFR's own chart (PFR omits down and goal-to-go context). | |
| -- Verified against real 2026 data 2026-09-24: 162 TD plays, all 32 teams, wks 1-2. | |
| -- | |
| -- Reuses the table design from the earlier great-lakes-v1 work | |
| -- (sql-data-lake-builder/schema/nfl_touchdown_logs.sql) rather than reinventing it. | |
| -- | |
| -- Engine: DuckDB. Depends on sql/nfl_pfr_schema.sql for the bronze/silver schemas. | |
| -- ============================================================================= | |
| CREATE SCHEMA IF NOT EXISTS silver; | |
| -- One row per touchdown play. This is the grain PFR's TD logs are built from. | |
| CREATE TABLE IF NOT EXISTS silver.nfl_td_log ( | |
| season INTEGER NOT NULL, | |
| week INTEGER NOT NULL, | |
| season_type VARCHAR, | |
| game_id VARCHAR NOT NULL, | |
| play_id INTEGER NOT NULL, | |
| game_date DATE, | |
| -- who scored / who conceded | |
| scoring_team VARCHAR NOT NULL, -- td_team | |
| defense_team VARCHAR, -- team that allowed it | |
| is_offensive_td BOOLEAN, -- scoring team had possession | |
| -- scorer | |
| scorer_player_id VARCHAR, | |
| scorer_player_name VARCHAR, | |
| td_type VARCHAR, -- pass | rush | return | other | |
| -- game context | |
| qtr INTEGER, | |
| clock VARCHAR, -- game clock at the play | |
| drive INTEGER, | |
| -- situational context (this is the part PFR's chart does NOT give you) | |
| yardline_100 INTEGER, -- yards from opponent goal line | |
| is_red_zone BOOLEAN, -- yardline_100 <= 20 | |
| is_goal_to_go BOOLEAN, | |
| down INTEGER, | |
| is_third_down BOOLEAN, | |
| -- passing detail when applicable | |
| passer_player_id VARCHAR, | |
| passer_player_name VARCHAR, | |
| -- score state after the play | |
| posteam_score_post INTEGER, | |
| defteam_score_post INTEGER, | |
| play_description VARCHAR, | |
| ingest_run_id VARCHAR NOT NULL, | |
| PRIMARY KEY (season, game_id, play_id) | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_td_log_scoring ON silver.nfl_td_log (season, scoring_team, week); | |
| CREATE INDEX IF NOT EXISTS ix_td_log_defense ON silver.nfl_td_log (season, defense_team, week); | |
| CREATE INDEX IF NOT EXISTS ix_td_log_scorer ON silver.nfl_td_log (season, scorer_player_id); | |
| -- ============================================================================= | |
| -- PFR #all_team_td_log -- touchdowns a team SCORED | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW silver.v_nfl_team_td_log AS | |
| SELECT season, week, game_id, game_date, | |
| scoring_team AS team, | |
| defense_team AS opponent, | |
| qtr, clock, drive, | |
| scorer_player_name, scorer_player_id, td_type, | |
| passer_player_name, | |
| yardline_100, is_red_zone, is_goal_to_go, down, is_third_down, | |
| play_description | |
| FROM silver.nfl_td_log | |
| ORDER BY season, week, game_id, qtr; | |
| -- ============================================================================= | |
| -- PFR #all_opp_td_log -- touchdowns a team ALLOWED | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW silver.v_nfl_opp_td_log AS | |
| SELECT season, week, game_id, game_date, | |
| defense_team AS team, -- the team that gave it up | |
| scoring_team AS opponent, | |
| qtr, clock, drive, | |
| scorer_player_name, scorer_player_id, td_type, | |
| passer_player_name, | |
| yardline_100, is_red_zone, is_goal_to_go, down, is_third_down, | |
| play_description | |
| FROM silver.nfl_td_log | |
| WHERE defense_team IS NOT NULL | |
| ORDER BY season, week, game_id, qtr; | |
| -- ============================================================================= | |
| -- Rollups: scored vs. allowed per team, with situational splits. | |
| -- This is the shape that actually feeds a matchup edge -- a team that concedes | |
| -- red-zone TDs at a high rate against a team that reaches the red zone often is | |
| -- where a total or anytime-TD prop gets mispriced. | |
| -- ============================================================================= | |
| CREATE OR REPLACE VIEW silver.v_nfl_td_scored_rollup AS | |
| SELECT season, scoring_team AS team, | |
| count(*) AS tds_scored, | |
| sum(CASE WHEN td_type='pass' THEN 1 ELSE 0 END) AS passing_tds, | |
| sum(CASE WHEN td_type='rush' THEN 1 ELSE 0 END) AS rushing_tds, | |
| sum(CASE WHEN td_type='return' THEN 1 ELSE 0 END) AS return_tds, | |
| sum(CASE WHEN is_red_zone THEN 1 ELSE 0 END) AS red_zone_tds, | |
| sum(CASE WHEN is_goal_to_go THEN 1 ELSE 0 END) AS goal_to_go_tds, | |
| sum(CASE WHEN is_third_down THEN 1 ELSE 0 END) AS third_down_tds, | |
| count(DISTINCT week) AS weeks_played, | |
| 1.0 * count(*) / NULLIF(count(DISTINCT game_id), 0) AS tds_per_game | |
| FROM silver.nfl_td_log | |
| GROUP BY season, scoring_team; | |
| CREATE OR REPLACE VIEW silver.v_nfl_td_allowed_rollup AS | |
| SELECT season, defense_team AS team, | |
| count(*) AS tds_allowed, | |
| sum(CASE WHEN td_type='pass' THEN 1 ELSE 0 END) AS passing_tds_allowed, | |
| sum(CASE WHEN td_type='rush' THEN 1 ELSE 0 END) AS rushing_tds_allowed, | |
| sum(CASE WHEN td_type='return' THEN 1 ELSE 0 END) AS return_tds_allowed, | |
| sum(CASE WHEN is_red_zone THEN 1 ELSE 0 END) AS red_zone_tds_allowed, | |
| sum(CASE WHEN is_goal_to_go THEN 1 ELSE 0 END) AS goal_to_go_tds_allowed, | |
| sum(CASE WHEN is_third_down THEN 1 ELSE 0 END) AS third_down_tds_allowed, | |
| count(DISTINCT week) AS weeks_played, | |
| 1.0 * count(*) / NULLIF(count(DISTINCT game_id), 0) AS tds_allowed_per_game | |
| FROM silver.nfl_td_log | |
| WHERE defense_team IS NOT NULL | |
| GROUP BY season, defense_team; | |
| -- Both sides of the ball in one row per team -- the matchup view. | |
| CREATE OR REPLACE VIEW silver.v_nfl_td_team_summary AS | |
| SELECT COALESCE(s.season, a.season) AS season, | |
| COALESCE(s.team, a.team) AS team, | |
| s.tds_scored, s.red_zone_tds, s.goal_to_go_tds, s.third_down_tds, | |
| s.tds_per_game, | |
| a.tds_allowed, a.red_zone_tds_allowed, a.goal_to_go_tds_allowed, | |
| a.third_down_tds_allowed, a.tds_allowed_per_game, | |
| COALESCE(s.tds_scored, 0) - COALESCE(a.tds_allowed, 0) AS td_differential | |
| FROM silver.v_nfl_td_scored_rollup s | |
| FULL OUTER JOIN silver.v_nfl_td_allowed_rollup a | |
| ON s.season = a.season AND s.team = a.team; | |
| -- Anytime-TD scorer leaderboard -- directly comparable to an anytime-TD prop. | |
| CREATE OR REPLACE VIEW silver.v_nfl_anytime_td_scorers AS | |
| SELECT season, scorer_player_id, scorer_player_name, | |
| ANY_VALUE(scoring_team) AS team, | |
| count(*) AS total_tds, | |
| count(DISTINCT game_id) AS games_with_td, | |
| sum(CASE WHEN td_type='rush' THEN 1 ELSE 0 END) AS rushing_tds, | |
| sum(CASE WHEN td_type='pass' THEN 1 ELSE 0 END) AS receiving_tds, | |
| sum(CASE WHEN is_red_zone THEN 1 ELSE 0 END) AS red_zone_tds | |
| FROM silver.nfl_td_log | |
| WHERE scorer_player_id IS NOT NULL | |
| GROUP BY season, scorer_player_id, scorer_player_name | |
| ORDER BY total_tds DESC; | |