#!/usr/bin/env python3 """ Compute Clutch Player Performance Splits Analyzes player performance in close games and clutch situations Fixes: Q18, Q31-34, Q37 "How does this player perform in crunch time?" Supports: NBA, NHL, NFL (key players), NCAAB """ import psycopg2 from datetime import datetime, timedelta def load_db_url(): with open('/var/www/html/eventheodds/.env', 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().split('?')[0] return '' def compute_clutch_splits(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create PlayerClutchSplits table cur.execute(''' CREATE TABLE IF NOT EXISTS "PlayerClutchSplits" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), "playerExternalId" TEXT NOT NULL, "playerName" VARCHAR(200), team VARCHAR(100), -- Close game performance "closeGames" INTEGER DEFAULT 0, "closeGameAvgPts" NUMERIC, "closeGameAvgMin" NUMERIC, "closeGameUsageBoost" NUMERIC, -- Blowout vs close comparison "blowoutGames" INTEGER DEFAULT 0, "blowoutAvgPts" NUMERIC, -- Clutch differential "clutchDifferential" NUMERIC, "clutchRating" INTEGER, "clutchTier" VARCHAR(30), -- Win contribution "closeGameWins" INTEGER DEFAULT 0, "closeGameLosses" INTEGER DEFAULT 0, "winContribution" NUMERIC, "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "playerExternalId", season) ) ''') conn.commit() print("PlayerClutchSplits table created/verified") # Sport configurations - lower thresholds for early season sports_config = { 'nba': {'pts_key': 'espn_pts', 'min_key': 'espn_min', 'min_games': 2}, 'ncaab': {'pts_key': 'espn_pts', 'min_key': 'espn_min', 'min_games': 2}, 'nhl': {'pts_key': 'nhl_pts', 'min_key': 'nhl_toi', 'min_games': 2}, } for league, config in sports_config.items(): print(f"\nComputing {league.upper()} clutch player splits...") # Join player stats with game state tracking via date + team cur.execute(''' WITH player_game_context AS ( SELECT pgm.league, pgm."playerExternalId", pgm."gameDate", pgm.team, MAX(CASE WHEN pgm."statKey" = %s THEN pgm.value END) as pts, MAX(CASE WHEN pgm."statKey" IN (%s, 'minutes', 'nba_min') THEN pgm.value END) as mins, -- Get game state gst."closeGame", gst.blowout, CASE WHEN pgm.team = gst."homeTeam" THEN CASE WHEN gst."finalHomeScore" > gst."finalAwayScore" THEN true ELSE false END ELSE CASE WHEN gst."finalAwayScore" > gst."finalHomeScore" THEN true ELSE false END END as won FROM "PlayerGameMetric" pgm JOIN "GameStateTracking" gst ON gst.league = pgm.league AND gst."gameDate" = pgm."gameDate" AND (gst."homeTeam" = pgm.team OR gst."awayTeam" = pgm.team) WHERE pgm.league = %s AND pgm."statKey" IN (%s, %s, 'minutes', 'nba_min') GROUP BY pgm.league, pgm."playerExternalId", pgm."gameDate", pgm.team, gst."closeGame", gst.blowout, gst."homeTeam", gst."awayTeam", gst."finalHomeScore", gst."finalAwayScore" HAVING MAX(CASE WHEN pgm."statKey" IN (%s, 'minutes', 'nba_min') THEN pgm.value END) > 5 ), player_splits AS ( SELECT league, "playerExternalId", EXTRACT(YEAR FROM "gameDate")::text as season, team, -- Close games COUNT(CASE WHEN "closeGame" THEN 1 END) as close_games, ROUND(AVG(CASE WHEN "closeGame" THEN pts END)::numeric, 1) as close_pts, ROUND(AVG(CASE WHEN "closeGame" THEN mins END)::numeric, 1) as close_mins, -- Blowouts COUNT(CASE WHEN blowout THEN 1 END) as blowout_games, ROUND(AVG(CASE WHEN blowout THEN pts END)::numeric, 1) as blowout_pts, -- All games baseline ROUND(AVG(pts)::numeric, 1) as overall_avg_pts, -- Close game wins/losses COUNT(CASE WHEN "closeGame" AND won THEN 1 END) as close_wins, COUNT(CASE WHEN "closeGame" AND NOT won THEN 1 END) as close_losses FROM player_game_context WHERE pts IS NOT NULL GROUP BY league, "playerExternalId", EXTRACT(YEAR FROM "gameDate"), team HAVING COUNT(CASE WHEN "closeGame" THEN 1 END) >= %s ) INSERT INTO "PlayerClutchSplits" (league, season, "playerExternalId", team, "closeGames", "closeGameAvgPts", "closeGameAvgMin", "closeGameUsageBoost", "blowoutGames", "blowoutAvgPts", "clutchDifferential", "clutchRating", "clutchTier", "closeGameWins", "closeGameLosses", "winContribution") SELECT league, season, "playerExternalId", team, close_games, close_pts, close_mins, -- Usage boost in close games vs blowouts ROUND((close_pts - COALESCE(blowout_pts, overall_avg_pts)) / NULLIF(COALESCE(blowout_pts, overall_avg_pts), 0) * 100, 1), blowout_games, blowout_pts, -- Clutch differential (close game pts vs overall) ROUND(close_pts - overall_avg_pts, 1), -- Clutch rating (0-100) LEAST(100, GREATEST(0, ( 50 + (close_pts - overall_avg_pts) * 5 + (COALESCE(close_wins::numeric / NULLIF(close_wins + close_losses, 0), 0.5) - 0.5) * 50 )))::int, CASE WHEN close_pts > overall_avg_pts * 1.15 THEN 'ELITE_CLUTCH' WHEN close_pts > overall_avg_pts * 1.05 THEN 'CLUTCH_PERFORMER' WHEN close_pts >= overall_avg_pts * 0.95 THEN 'CONSISTENT' WHEN close_pts >= overall_avg_pts * 0.85 THEN 'SLIGHT_DIP' ELSE 'STRUGGLES_CLUTCH' END, close_wins, close_losses, ROUND(close_wins::numeric / NULLIF(close_wins + close_losses, 0), 3) FROM player_splits ON CONFLICT (league, "playerExternalId", season) DO UPDATE SET team = EXCLUDED.team, "closeGames" = EXCLUDED."closeGames", "closeGameAvgPts" = EXCLUDED."closeGameAvgPts", "closeGameAvgMin" = EXCLUDED."closeGameAvgMin", "closeGameUsageBoost" = EXCLUDED."closeGameUsageBoost", "blowoutGames" = EXCLUDED."blowoutGames", "blowoutAvgPts" = EXCLUDED."blowoutAvgPts", "clutchDifferential" = EXCLUDED."clutchDifferential", "clutchRating" = EXCLUDED."clutchRating", "clutchTier" = EXCLUDED."clutchTier", "closeGameWins" = EXCLUDED."closeGameWins", "closeGameLosses" = EXCLUDED."closeGameLosses", "winContribution" = EXCLUDED."winContribution", "updatedAt" = NOW() ''', ( config['pts_key'], config['min_key'], league, config['pts_key'], config['min_key'], config['min_key'], config['min_games'] )) count = cur.rowcount conn.commit() print(f" {league.upper()}: {count} player clutch records") # Add player names print("\nAdding player names...") cur.execute(''' UPDATE "PlayerClutchSplits" pcs SET "playerName" = p.name FROM "Player" p WHERE pcs."playerExternalId" = p."externalPlayerId" AND pcs."playerName" IS NULL ''') conn.commit() # Summary cur.execute(''' SELECT league, "clutchTier", COUNT(*), ROUND(AVG("clutchRating")::numeric, 0) FROM "PlayerClutchSplits" WHERE "clutchTier" IS NOT NULL GROUP BY league, "clutchTier" ORDER BY league, "clutchTier" ''') print("\n\nPlayer Clutch Distribution:") current_league = None for row in cur.fetchall(): if row[0] != current_league: current_league = row[0] print(f"\n {current_league.upper()}:") print(f" {row[1]}: {row[2]} players (avg rating: {row[3]})") # Show top clutch performers cur.execute(''' SELECT "playerName", team, "clutchRating", "clutchDifferential", "closeGameWinPct" FROM ( SELECT "playerName", team, "clutchRating", "clutchDifferential", ROUND("closeGameWins"::numeric / NULLIF("closeGameWins" + "closeGameLosses", 0) * 100, 0) as "closeGameWinPct" FROM "PlayerClutchSplits" WHERE "closeGames" >= 5 AND "playerName" IS NOT NULL ORDER BY "clutchRating" DESC LIMIT 10 ) sub ''') results = cur.fetchall() if results: print("\n\nTop Clutch Performers:") for row in results: print(f" {row[0]} ({row[1]}): Rating {row[2]}, +{row[3]} pts/gm, {row[4]}% close W") cur.execute('SELECT COUNT(*) FROM "PlayerClutchSplits"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\n✅ Clutch player splits complete: {total} player records") if __name__ == '__main__': compute_clutch_splits()