CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL; CREATE TABLE sportsbooks(sportsbook_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,api_key VARCHAR(255),is_active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE sports(sport_id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,league VARCHAR(50),season_type VARCHAR(20),is_active BOOLEAN DEFAULT TRUE);CREATE TABLE teams(team_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),name VARCHAR(100) NOT NULL,abbreviation VARCHAR(10),city VARCHAR(100),conference VARCHAR(50),division VARCHAR(50),elo_rating NUMERIC(8,2) DEFAULT 1500.00,win_pct NUMERIC(5,4),pythagorean_wins NUMERIC(6,2));CREATE TABLE players(player_id SERIAL PRIMARY KEY,team_id INT REFERENCES teams(team_id),name VARCHAR(150) NOT NULL,position VARCHAR(20),jersey_number INT,height_inches INT,weight_lbs INT,is_active BOOLEAN DEFAULT TRUE,injury_status VARCHAR(30),fantasy_points_avg NUMERIC(8,2));CREATE TABLE games(game_id SERIAL PRIMARY KEY,sport_id INT REFERENCES sports(sport_id),home_team_id INT REFERENCES teams(team_id),away_team_id INT REFERENCES teams(team_id),scheduled_at TIMESTAMPTZ NOT NULL,venue VARCHAR(200),status VARCHAR(20) DEFAULT 'scheduled',home_score INT,away_score INT,period INT,is_neutral_site BOOLEAN DEFAULT FALSE,weather_temp_f NUMERIC(5,1),weather_wind_mph NUMERIC(5,1),weather_precip_pct NUMERIC(5,2));CREATE TABLE odds_snapshots(snapshot_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30) NOT NULL,selection VARCHAR(50),odds_american INT,odds_decimal NUMERIC(8,4),odds_fractional VARCHAR(20),implied_probability NUMERIC(6,5),spread_value NUMERIC(5,1),total_value NUMERIC(6,1),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bets(bet_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),market_type VARCHAR(30),selection VARCHAR(50),stake NUMERIC(10,2) NOT NULL,odds_decimal NUMERIC(8,4),potential_payout NUMERIC(12,2),result VARCHAR(10),profit_loss NUMERIC(12,2),placed_at TIMESTAMPTZ DEFAULT NOW(),settled_at TIMESTAMPTZ,model_confidence NUMERIC(5,4),edge_pct NUMERIC(6,4),kelly_fraction NUMERIC(6,4));CREATE TABLE model_predictions(prediction_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),model_version VARCHAR(30),predicted_home_win_prob NUMERIC(6,5),predicted_away_win_prob NUMERIC(6,5),predicted_spread NUMERIC(5,1),predicted_total NUMERIC(6,1),predicted_home_score NUMERIC(6,2),predicted_away_score NUMERIC(6,2),feature_vector JSONB,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE bankroll_ledger(ledger_id SERIAL PRIMARY KEY,bet_id INT REFERENCES bets(bet_id),transaction_type VARCHAR(20),amount NUMERIC(12,2),balance_after NUMERIC(12,2),notes TEXT,created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE player_props(prop_id SERIAL PRIMARY KEY,game_id INT REFERENCES games(game_id),player_id INT REFERENCES players(player_id),sportsbook_id INT REFERENCES sportsbooks(sportsbook_id),stat_type VARCHAR(30),line_value NUMERIC(6,1),over_odds INT,under_odds INT,predicted_value NUMERIC(8,2),edge_over NUMERIC(6,4),edge_under NUMERIC(6,4),captured_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE parlay_legs(leg_id SERIAL PRIMARY KEY,parlay_id INT,game_id INT REFERENCES games(game_id),market_type VARCHAR(30),selection VARCHAR(50),odds_decimal NUMERIC(8,4),result VARCHAR(10));CREATE INDEX idx_odds_game_market ON odds_snapshots(game_id,market_type,captured_at DESC);CREATE INDEX idx_bets_game ON bets(game_id,placed_at DESC);CREATE INDEX idx_predictions_game ON model_predictions(game_id,model_version);CREATE INDEX idx_props_player ON player_props(player_id,game_id);WITH recent_odds AS(SELECT game_id,sportsbook_id,market_type,selection,odds_decimal,implied_probability,captured_at,ROW_NUMBER()OVER(PARTITION BY game_id,sportsbook_id,market_type,selection ORDER BY captured_at DESC)AS rn FROM odds_snapshots WHERE captured_at>=NOW()-INTERVAL'24 hours'),best_lines AS(SELECT game_id,market_type,selection,MAX(odds_decimal)AS best_odds,MIN(implied_probability)AS lowest_vig_prob FROM recent_odds WHERE rn=1 GROUP BY game_id,market_type,selection)SELECT g.game_id,ht.name AS home_team,at.name AS away_team,g.scheduled_at,bl.market_type,bl.selection,bl.best_odds,bl.lowest_vig_prob,mp.predicted_home_win_prob,mp.predicted_away_win_prob,CASE WHEN bl.selection='home' THEN mp.predicted_home_win_prob-bl.lowest_vig_prob WHEN bl.selection='away' THEN mp.predicted_away_win_prob-bl.lowest_vig_prob ELSE 0 END AS edge,CASE WHEN bl.selection='home' THEN GREATEST(0,(mp.predicted_home_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))WHEN bl.selection='away' THEN GREATEST(0,(mp.predicted_away_win_prob-(1.0/bl.best_odds))/(1.0-(1.0/bl.best_odds)))ELSE 0 END AS kelly_fraction FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN best_lines bl ON g.game_id=bl.game_id LEFT JOIN model_predictions mp ON g.game_id=mp.game_id AND mp.model_version='v3.2.1' WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'48 hours' ORDER BY edge DESC;WITH daily_results AS(SELECT DATE(settled_at)AS settle_date,COUNT(*)AS total_bets,SUM(CASE WHEN result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN result='loss' THEN 1 ELSE 0 END)AS losses,SUM(CASE WHEN result='push' THEN 1 ELSE 0 END)AS pushes,SUM(stake)AS total_staked,SUM(profit_loss)AS daily_pnl,AVG(model_confidence)AS avg_confidence,AVG(edge_pct)AS avg_edge FROM bets WHERE settled_at IS NOT NULL GROUP BY DATE(settled_at)),rolling_metrics AS(SELECT settle_date,total_bets,wins,losses,daily_pnl,total_staked,SUM(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS cumulative_pnl,AVG(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_avg_pnl,SUM(total_bets)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_bets,SUM(wins)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_wins,STDDEV(daily_pnl)OVER(ORDER BY settle_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)AS rolling_30d_stddev,avg_confidence,avg_edge FROM daily_results)SELECT settle_date,total_bets,wins,losses,daily_pnl,cumulative_pnl,rolling_30d_avg_pnl,CASE WHEN rolling_30d_stddev>0 THEN rolling_30d_avg_pnl/rolling_30d_stddev ELSE 0 END AS sharpe_ratio_30d,CASE WHEN rolling_30d_bets>0 THEN ROUND(rolling_30d_wins::NUMERIC/rolling_30d_bets,4)ELSE 0 END AS win_rate_30d,ROUND(daily_pnl/NULLIF(total_staked,0)*100,2)AS roi_pct,avg_confidence,avg_edge FROM rolling_metrics ORDER BY settle_date DESC;WITH line_movements AS(SELECT os.game_id,os.sportsbook_id,sb.name AS book_name,os.market_type,os.selection,os.odds_decimal,os.implied_probability,os.spread_value,os.total_value,os.captured_at,LAG(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_odds,LAG(os.implied_probability)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_prob,LAG(os.spread_value)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS prev_spread,FIRST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at)AS opening_odds,LAST_VALUE(os.odds_decimal)OVER(PARTITION BY os.game_id,os.sportsbook_id,os.market_type,os.selection ORDER BY os.captured_at RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS closing_odds FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.game_id IN(SELECT game_id FROM games WHERE scheduled_at>=NOW()-INTERVAL'7 days'))SELECT game_id,book_name,market_type,selection,opening_odds,closing_odds,closing_odds-opening_odds AS odds_movement,COUNT(*)AS snapshot_count,MAX(odds_decimal)-MIN(odds_decimal)AS odds_range,AVG(ABS(COALESCE(odds_decimal-prev_odds,0)))AS avg_tick_size FROM line_movements GROUP BY game_id,book_name,market_type,selection,opening_odds,closing_odds HAVING COUNT(*)>3 ORDER BY ABS(closing_odds-opening_odds)DESC;WITH player_baselines AS(SELECT pp.player_id,p.name AS player_name,p.position,pp.stat_type,AVG(pp.predicted_value)AS avg_predicted,STDDEV(pp.predicted_value)AS stddev_predicted,AVG(pp.line_value)AS avg_line,COUNT(*)AS sample_size,AVG(pp.edge_over)AS avg_edge_over,AVG(pp.edge_under)AS avg_edge_under FROM player_props pp JOIN players p ON pp.player_id=p.player_id WHERE pp.captured_at>=NOW()-INTERVAL'30 days' GROUP BY pp.player_id,p.name,p.position,pp.stat_type HAVING COUNT(*)>=5),prop_rankings AS(SELECT player_name,position,stat_type,avg_predicted,avg_line,avg_predicted-avg_line AS diff_from_line,avg_edge_over,avg_edge_under,sample_size,NTILE(10)OVER(PARTITION BY stat_type ORDER BY avg_edge_over DESC)AS edge_decile,PERCENT_RANK()OVER(PARTITION BY stat_type ORDER BY avg_edge_over)AS edge_percentile FROM player_baselines WHERE stddev_predicted>0)SELECT player_name,position,stat_type,ROUND(avg_predicted,1)AS predicted,ROUND(avg_line,1)AS line,ROUND(diff_from_line,1)AS diff,ROUND(avg_edge_over*100,2)AS edge_over_pct,ROUND(avg_edge_under*100,2)AS edge_under_pct,edge_decile,ROUND(edge_percentile*100,1)AS edge_pctile,sample_size FROM prop_rankings WHERE edge_decile<=2 ORDER BY edge_over_pct DESC;WITH game_features AS(SELECT g.game_id,g.scheduled_at,ht.elo_rating AS home_elo,at.elo_rating AS away_elo,ht.elo_rating-at.elo_rating AS elo_diff,ht.win_pct AS home_win_pct,at.win_pct AS away_win_pct,ht.pythagorean_wins AS home_pyth,at.pythagorean_wins AS away_pyth,g.weather_temp_f,g.weather_wind_mph,g.weather_precip_pct,g.is_neutral_site,EXTRACT(DOW FROM g.scheduled_at)AS day_of_week,EXTRACT(HOUR FROM g.scheduled_at)AS hour_of_day,LAG(g.scheduled_at)OVER(PARTITION BY g.home_team_id ORDER BY g.scheduled_at)AS home_prev_game,LAG(g.scheduled_at)OVER(PARTITION BY g.away_team_id ORDER BY g.scheduled_at)AS away_prev_game FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'2 years'),enriched AS(SELECT gf.*,EXTRACT(EPOCH FROM gf.scheduled_at-gf.home_prev_game)/86400.0 AS home_rest_days,EXTRACT(EPOCH FROM gf.scheduled_at-gf.away_prev_game)/86400.0 AS away_rest_days,g.home_score,g.away_score,g.home_score-g.away_score AS margin,CASE WHEN g.home_score>g.away_score THEN 1 ELSE 0 END AS home_win,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total FROM game_features gf JOIN games g ON gf.game_id=g.game_id LEFT JOIN model_predictions mp ON gf.game_id=mp.game_id AND mp.model_version='v3.2.1')SELECT CORR(elo_diff,margin)AS elo_margin_corr,CORR(predicted_home_win_prob,home_win)AS prob_calibration,CORR(predicted_spread,margin)AS spread_accuracy,CORR(predicted_total,home_score+away_score)AS total_accuracy,AVG(CASE WHEN(predicted_home_win_prob>0.5 AND home_win=1)OR(predicted_home_win_prob<0.5 AND home_win=0)THEN 1.0 ELSE 0.0 END)AS model_accuracy,COUNT(*)AS total_games FROM enriched WHERE predicted_home_win_prob IS NOT NULL;WITH parlay_analysis AS(SELECT p.parlay_id,COUNT(*)AS leg_count,EXP(SUM(LN(pl.odds_decimal)))AS combined_odds,1.0/EXP(SUM(LN(pl.odds_decimal)))AS implied_prob,BOOL_AND(pl.result='win')AS parlay_won,SUM(CASE WHEN pl.result='win' THEN 1 ELSE 0 END)AS legs_won,SUM(CASE WHEN pl.result='loss' THEN 1 ELSE 0 END)AS legs_lost FROM parlay_legs pl JOIN(SELECT DISTINCT parlay_id FROM parlay_legs)p ON pl.parlay_id=p.parlay_id GROUP BY p.parlay_id)SELECT leg_count,COUNT(*)AS total_parlays,SUM(CASE WHEN parlay_won THEN 1 ELSE 0 END)AS parlays_won,ROUND(AVG(CASE WHEN parlay_won THEN 1.0 ELSE 0.0 END)*100,2)AS win_rate_pct,ROUND(AVG(combined_odds),2)AS avg_combined_odds,ROUND(AVG(implied_prob)*100,2)AS avg_implied_prob_pct,ROUND(AVG(legs_won::NUMERIC/leg_count)*100,2)AS avg_leg_hit_rate FROM parlay_analysis GROUP BY leg_count ORDER BY leg_count;WITH bankroll_series AS(SELECT bl.created_at,bl.balance_after,bl.transaction_type,bl.amount,b.model_confidence,b.edge_pct,b.market_type,LAG(bl.balance_after)OVER(ORDER BY bl.created_at)AS prev_balance,MAX(bl.balance_after)OVER(ORDER BY bl.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS running_peak FROM bankroll_ledger bl LEFT JOIN bets b ON bl.bet_id=b.bet_id)SELECT created_at,balance_after,transaction_type,amount,running_peak,running_peak-balance_after AS drawdown,CASE WHEN running_peak>0 THEN(running_peak-balance_after)/running_peak*100 ELSE 0 END AS drawdown_pct,model_confidence,edge_pct,market_type FROM bankroll_series ORDER BY created_at DESC LIMIT 500;SELECT sb.name AS sportsbook,COUNT(DISTINCT os.game_id)AS games_covered,AVG(CASE WHEN os.market_type='moneyline' THEN os.implied_probability END)AS avg_ml_implied_prob,AVG(CASE WHEN os.market_type='spread' THEN ABS(os.spread_value)END)AS avg_spread,AVG(CASE WHEN os.market_type='total' THEN os.total_value END)AS avg_total,SUM(CASE WHEN os.market_type='moneyline' THEN 1 ELSE 0 END)AS ml_snapshots,SUM(CASE WHEN os.market_type='spread' THEN 1 ELSE 0 END)AS spread_snapshots,SUM(CASE WHEN os.market_type='total' THEN 1 ELSE 0 END)AS total_snapshots FROM odds_snapshots os JOIN sportsbooks sb ON os.sportsbook_id=sb.sportsbook_id WHERE os.captured_at>=NOW()-INTERVAL'30 days' GROUP BY sb.name ORDER BY games_covered DESC;WITH elo_updates AS(SELECT g.game_id,g.home_team_id,g.away_team_id,g.home_score,g.away_score,ht.elo_rating AS home_elo_pre,at.elo_rating AS away_elo_pre,1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0))AS expected_home,CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END AS actual_home,20*(CASE WHEN g.home_score>g.away_score THEN 1.0 WHEN g.home_score=g.away_score THEN 0.5 ELSE 0.0 END-1.0/(1.0+POWER(10,(at.elo_rating-ht.elo_rating-65)/400.0)))AS home_elo_delta FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id WHERE g.status='final' ORDER BY g.scheduled_at DESC LIMIT 100)SELECT game_id,home_team_id,away_team_id,ROUND(home_elo_pre,1)AS home_elo,ROUND(away_elo_pre,1)AS away_elo,ROUND(expected_home,4)AS exp_home_win,ROUND(actual_home,1)AS actual,ROUND(home_elo_delta,2)AS elo_change,home_score,away_score FROM elo_updates;WITH clv_analysis AS(SELECT b.bet_id,b.game_id,b.market_type,b.selection,b.odds_decimal AS bet_odds,b.placed_at,closing.odds_decimal AS closing_odds,1.0/b.odds_decimal AS bet_implied_prob,1.0/closing.odds_decimal AS closing_implied_prob,(1.0/closing.odds_decimal)-(1.0/b.odds_decimal)AS clv,b.stake,b.profit_loss,b.model_confidence,b.edge_pct FROM bets b CROSS JOIN LATERAL(SELECT os.odds_decimal FROM odds_snapshots os WHERE os.game_id=b.game_id AND os.market_type=b.market_type AND os.selection=b.selection ORDER BY os.captured_at DESC LIMIT 1)closing WHERE b.settled_at IS NOT NULL)SELECT market_type,COUNT(*)AS total_bets,ROUND(AVG(clv)*100,3)AS avg_clv_pct,ROUND(SUM(CASE WHEN clv>0 THEN 1.0 ELSE 0.0 END)/COUNT(*)*100,1)AS pct_beating_close,ROUND(AVG(profit_loss),2)AS avg_pnl,ROUND(SUM(profit_loss),2)AS total_pnl,ROUND(CORR(clv,profit_loss),4)AS clv_pnl_correlation,ROUND(AVG(model_confidence),4)AS avg_model_conf FROM clv_analysis GROUP BY market_type ORDER BY avg_clv_pct DESC;WITH RECURSIVE fibonacci_bankroll(n,fib_prev,fib_curr,unit_size)AS(SELECT 1,0,1,10.00 UNION ALL SELECT n+1,fib_curr,fib_prev+fib_curr,10.00*(fib_prev+fib_curr)FROM fibonacci_bankroll WHERE n<20)SELECT n,fib_curr AS fibonacci_number,ROUND(unit_size,2)AS stake_at_level FROM fibonacci_bankroll;SELECT DATE_TRUNC('week',b.placed_at)AS week,s.name AS sport,b.market_type,COUNT(*)AS bets,SUM(b.stake)AS total_staked,SUM(b.profit_loss)AS pnl,ROUND(SUM(b.profit_loss)/NULLIF(SUM(b.stake),0)*100,2)AS roi_pct,SUM(CASE WHEN b.result='win' THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN b.result='loss' THEN 1 ELSE 0 END)AS losses,ROUND(AVG(b.kelly_fraction),4)AS avg_kelly,ROUND(AVG(b.edge_pct)*100,2)AS avg_edge_pct,ROUND(STDDEV(b.profit_loss),2)AS pnl_stddev FROM bets b JOIN games g ON b.game_id=g.game_id JOIN sports s ON g.sport_id=s.sport_id WHERE b.settled_at IS NOT NULL GROUP BY DATE_TRUNC('week',b.placed_at),s.name,b.market_type ORDER BY week DESC,pnl DESC;WITH market_efficiency AS(SELECT os.game_id,os.market_type,os.sportsbook_id,AVG(os.implied_probability)AS avg_implied,STDDEV(os.implied_probability)AS stddev_implied,COUNT(DISTINCT os.sportsbook_id)AS books_quoting,SUM(CASE WHEN os.selection='home' THEN os.implied_probability WHEN os.selection='away' THEN os.implied_probability ELSE 0 END)AS total_implied,g.home_score,g.away_score,CASE WHEN g.home_score>g.away_score THEN 'home' WHEN g.away_score>g.home_score THEN 'away' ELSE 'push' END AS actual_winner FROM odds_snapshots os JOIN games g ON os.game_id=g.game_id WHERE g.status='final' AND os.captured_at=(SELECT MAX(os2.captured_at)FROM odds_snapshots os2 WHERE os2.game_id=os.game_id AND os2.sportsbook_id=os.sportsbook_id AND os2.market_type=os.market_type AND os2.selection=os.selection)GROUP BY os.game_id,os.market_type,os.sportsbook_id,g.home_score,g.away_score)SELECT market_type,ROUND(AVG(total_implied),4)AS avg_overround,ROUND(AVG(stddev_implied),4)AS avg_line_variance,ROUND(AVG(books_quoting),1)AS avg_books,COUNT(DISTINCT game_id)AS games_analyzed FROM market_efficiency GROUP BY market_type ORDER BY avg_overround;CREATE MATERIALIZED VIEW mv_team_form AS SELECT t.team_id,t.name,t.abbreviation,COUNT(*)FILTER(WHERE g.status='final')AS games_played,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score>g.away_score)OR(g.away_team_id=t.team_id AND g.away_score>g.home_score)THEN 1 ELSE 0 END)AS wins,SUM(CASE WHEN(g.home_team_id=t.team_id AND g.home_score<g.away_score)OR(g.away_team_id=t.team_id AND g.away_score<g.home_score)THEN 1 ELSE 0 END)AS losses,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score-g.away_score ELSE g.away_score-g.home_score END)AS avg_margin,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.home_score ELSE g.away_score END)AS avg_points_for,AVG(CASE WHEN g.home_team_id=t.team_id THEN g.away_score ELSE g.home_score END)AS avg_points_against FROM teams t JOIN games g ON t.team_id IN(g.home_team_id,g.away_team_id)WHERE g.status='final' AND g.scheduled_at>=NOW()-INTERVAL'90 days' GROUP BY t.team_id,t.name,t.abbreviation;REFRESH MATERIALIZED VIEW CONCURRENTLY mv_team_form;CREATE OR REPLACE FUNCTION calculate_expected_value(p_true_prob NUMERIC,p_odds_decimal NUMERIC)RETURNS NUMERIC LANGUAGE plpgsql AS $$BEGIN RETURN(p_true_prob*p_odds_decimal)-(1-p_true_prob);END;$$;CREATE OR REPLACE FUNCTION calculate_kelly(p_true_prob NUMERIC,p_odds_decimal NUMERIC,p_fraction NUMERIC DEFAULT 0.25)RETURNS NUMERIC LANGUAGE plpgsql AS $$DECLARE v_edge NUMERIC;v_kelly NUMERIC;BEGIN v_edge:=p_true_prob*p_odds_decimal-(1-p_true_prob);v_kelly:=v_edge/(p_odds_decimal-1);RETURN GREATEST(0,v_kelly*p_fraction);END;$$;SELECT g.game_id,ht.name||' vs '||at.name AS matchup,g.scheduled_at,mp.predicted_home_win_prob,mp.predicted_spread,mp.predicted_total,calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)AS ev_home,calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)AS ev_away,calculate_kelly(mp.predicted_home_win_prob,bl.best_home_odds)AS kelly_home,calculate_kelly(mp.predicted_away_win_prob,bl.best_away_odds)AS kelly_away FROM games g JOIN teams ht ON g.home_team_id=ht.team_id JOIN teams at ON g.away_team_id=at.team_id JOIN model_predictions mp ON g.game_id=mp.game_id JOIN LATERAL(SELECT MAX(CASE WHEN os.selection='home' THEN os.odds_decimal END)AS best_home_odds,MAX(CASE WHEN os.selection='away' THEN os.odds_decimal END)AS best_away_odds FROM odds_snapshots os WHERE os.game_id=g.game_id AND os.market_type='moneyline' AND os.captured_at>=NOW()-INTERVAL'1 hour')bl ON TRUE WHERE g.status='scheduled' AND g.scheduled_at BETWEEN NOW()AND NOW()+INTERVAL'24 hours' AND mp.model_version='v3.2.1' AND(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds)>0.02 OR calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds)>0.02)ORDER BY GREATEST(calculate_expected_value(mp.predicted_home_win_prob,bl.best_home_odds),calculate_expected_value(mp.predicted_away_win_prob,bl.best_away_odds))DESC;WITH streak_calc AS(SELECT b.bet_id,b.placed_at,b.result,b.profit_loss,b.market_type,SUM(CASE WHEN b.result!=LAG(b.result)OVER(ORDER BY b.placed_at)THEN 1 ELSE 0 END)OVER(ORDER BY b.placed_at)AS streak_group FROM bets b WHERE b.settled_at IS NOT NULL),streaks AS(SELECT result,streak_group,COUNT(*)AS streak_length,SUM(profit_loss)AS streak_pnl,MIN(placed_at)AS streak_start,MAX(placed_at)AS streak_end FROM streak_calc GROUP BY result,streak_group)SELECT result,MAX(streak_length)AS longest_streak,AVG(streak_length)AS avg_streak,ROUND(AVG(streak_pnl),2)AS avg_streak_pnl FROM streaks GROUP BY result;WITH monte_carlo_inputs AS(SELECT AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END)AS historical_win_rate,AVG(odds_decimal)AS avg_odds,STDDEV(profit_loss/stake)AS return_stddev,AVG(stake)AS avg_stake_size,COUNT(*)AS sample_size FROM bets WHERE settled_at IS NOT NULL AND placed_at>=NOW()-INTERVAL'180 days')SELECT ROUND(historical_win_rate,4)AS win_rate,ROUND(avg_odds,3)AS avg_odds,ROUND(return_stddev,4)AS return_vol,ROUND(avg_stake_size,2)AS avg_stake,sample_size,ROUND(historical_win_rate*avg_odds-1,4)AS expected_return_per_unit,ROUND(POWER(((1+historical_win_rate*avg_odds-1)/(1-(1-historical_win_rate))),sample_size)-1,4)AS kelly_growth_estimate FROM monte_carlo_inputs;CREATE TABLE IF NOT EXISTS simulation_runs(run_id SERIAL PRIMARY KEY,simulation_type VARCHAR(30),parameters JSONB,num_iterations INT,results JSONB,created_at TIMESTAMPTZ DEFAULT NOW());INSERT INTO simulation_runs(simulation_type,parameters,num_iterations,results)SELECT 'bankroll_trajectory',jsonb_build_object('win_rate',AVG(CASE WHEN result='win' THEN 1.0 ELSE 0.0 END),'avg_odds',AVG(odds_decimal),'kelly_frac',0.25,'initial_bankroll',10000),10000,jsonb_build_object('median_final',0,'p5_final',0,'p95_final',0,'bust_rate',0,'double_rate',0)FROM bets WHERE settled_at IS NOT NULL;
Snowflake
BigQuery
Redshift
Databricks
DuckDB
dbt
Linear
GitHub
Slack
Notion
Confluence