Spaces:
Paused
Paused
| -- Parlay leg probability -- HEURISTIC, hit-rate-vs-line only, NOT backtested. | |
| -- | |
| -- Added alongside the "HEY BIG PROPPA!" frontend build (see | |
| -- frontend/design/HANDOFF_CLAUDE_CODE.md sec 4 / sec 0 rule 3: "Do not | |
| -- invent derived metrics silently. If a chart needs a metric that no gold | |
| -- object provides, build it as a named, documented view in the lake, | |
| -- export it through scripts/export_ramp.py with its own _index.csv row, | |
| -- and label it in the UI as a model output.") | |
| -- | |
| -- Method, per the user's explicit choice (not usage/toxicity/redzone | |
| -- weighted): a player's probability for a prop leg is simply his hit rate | |
| -- against the current FanDuel line over his last 10 games (or fewer if | |
| -- fewer have been played this season). This mirrors exactly what | |
| -- backend/data.py's hit_rate_probability() computes at request time from | |
| -- the exported CSVs directly. | |
| -- | |
| -- WHY THIS FILE EXISTS BUT ISN'T WIRED INTO export_ramp.py YET: this | |
| -- session's container has no lake/nfl.duckdb (gitignored, not rebuildable | |
| -- here -- see docs/HANDOFF.md sec 5 and github.md for why) and this | |
| -- environment's network policy blocks the hosts the ingest chain needs. | |
| -- backend/data.py computes the identical hit-rate-vs-line logic directly | |
| -- against lake/gold/nfl/*.csv so the frontend has real numbers regardless. | |
| -- When the lake is next rebuilt on a machine with DuckDB + network access, | |
| -- run this file and add a CHARTS row for it in scripts/export_ramp.py | |
| -- (chart_name = 'parlay_leg_probability') so it becomes a real gold export | |
| -- like every other pond, instead of being backend-computed only. | |
| CREATE OR REPLACE VIEW gold.v_nfl_parlay_leg_probability AS | |
| WITH lines AS ( | |
| SELECT | |
| player_name, | |
| market_slug, | |
| line, | |
| book_slug, | |
| fetched_at_utc, | |
| ROW_NUMBER() OVER ( | |
| PARTITION BY player_name, market_slug | |
| ORDER BY fetched_at_utc DESC | |
| ) AS rn | |
| FROM gold.nfl_prop_line_rotowire | |
| WHERE book_slug = 'fanduel' | |
| ), | |
| current_fanduel_line AS ( | |
| SELECT player_name, market_slug, line | |
| FROM lines | |
| WHERE rn = 1 | |
| ), | |
| -- weekly stat unpivoted per market_slug this project already supports | |
| weekly AS ( | |
| SELECT player_id, team, week, 'rushyds' AS market_slug, rushing_yards AS value | |
| FROM silver.nfl_player_rushing_week | |
| UNION ALL | |
| SELECT player_id, team, week, 'recyds', receiving_yards | |
| FROM silver.nfl_player_receiving_week | |
| UNION ALL | |
| SELECT player_id, team, week, 'recs', receptions | |
| FROM silver.nfl_player_receiving_week | |
| UNION ALL | |
| SELECT player_id, team, week, 'passyds', passing_yards | |
| FROM silver.nfl_player_passing_week | |
| ), | |
| player_names AS ( | |
| SELECT player_id, player_display_name AS player_name | |
| FROM gold.nfl_player_photos_index -- or silver.nfl_player, whichever carries display name at rebuild time | |
| ), | |
| joined AS ( | |
| SELECT | |
| w.player_id, | |
| pn.player_name, | |
| w.market_slug, | |
| w.week, | |
| w.value, | |
| fl.line | |
| FROM weekly w | |
| JOIN player_names pn USING (player_id) | |
| JOIN current_fanduel_line fl | |
| ON fl.player_name = pn.player_name AND fl.market_slug = w.market_slug | |
| ), | |
| last10 AS ( | |
| SELECT *, | |
| ROW_NUMBER() OVER (PARTITION BY player_id, market_slug ORDER BY week DESC) AS rn | |
| FROM joined | |
| ) | |
| SELECT | |
| player_id, | |
| player_name, | |
| market_slug, | |
| line, | |
| COUNT(*) AS games_considered, | |
| SUM(CASE WHEN value >= line THEN 1 ELSE 0 END) AS games_hit, | |
| ROUND(SUM(CASE WHEN value >= line THEN 1 ELSE 0 END)::DOUBLE / COUNT(*), 3) AS probability, | |
| 'hit_rate_vs_line -- HEURISTIC, not backtested, v1 has no usage/toxicity/redzone weighting' AS method_label | |
| FROM last10 | |
| WHERE rn <= 10 | |
| GROUP BY player_id, player_name, market_slug, line; | |