Ever wondered if the '86 Celtics could beat the '96 Bulls? Wonder no more!
select A.*
from nba_elo_history.nba_elo A
union all
select
l.game_date as date,
2024 as season,
null as nuetral,
'r' as playoff,
l.hmTm as team1,
l.VsTm as team2,
r.home_team_elo_rating as elo1_pre,
r.visiting_team_elo_rating as elo2_pre,
null as elo_prob1,
null as elo_prob2,
case when l.home_team_score > l.visiting_team_score
then r.home_team_elo_rating - r.elo_change
else r.home_team_elo_rating + r.elo_change
end as elo1_post,
case when l.home_team_score > l.visiting_team_score
then r.visiting_team_elo_rating + r.elo_change
else r.visiting_team_elo_rating - r.elo_change
end as elo2_post,
l.home_team_score as score1,
l.visiting_team_score as score2
from src_nba_elo_rollforward r
left join src_nba_results_log l on r.game_id = l.game_id select A.season
from ${elo_history} A
group by all
order by A.season select C.* from (
select A.season, A.team1 as team
from ${elo_history} A
union all
select B.season, B.team2
from ${elo_history} B) as C
where C.season = ${inputs.team1_season_dd.value}
group by all
order by C.team select C.* from (
select A.season, A.team1 as team
from ${elo_history} AS A
union all
select B.season, B.team2
from ${elo_history} AS B ) AS C
where C.season = ${inputs.team2_season_dd.value}
group by all
order by C.team<Dropdown data={seasons} name=team1_season_dd value=season title="Team 1 Year"
<DropdownOption valueLabel="1986" value="1986" />
<Dropdown data={team1} name=team1_dd value=team title="Team 1"
<DropdownOption valueLabel="BOS" value="BOS" />
<Dropdown data={seasons} name=team2_season_dd value=season title="Team 2 Year"
<DropdownOption valueLabel="1996" value="1996" />
<Dropdown data={team2} name=team2_dd value=team title="Team 2"
<DropdownOption valueLabel="CHI" value="CHI" />
select * from ${elo_history}
where season = ${inputs.team1_season_dd.value}
and ( team1 = '${inputs.team1_dd.value}' OR team2 = '${inputs.team1_dd.value}')
order by date select * from ${elo_history}
where season = ${inputs.team2_season_dd.value}
and ( team1 = '${inputs.team2_dd.value}' OR team2 = '${inputs.team2_dd.value}')
order by date with cte_games AS (
select
team1,
team2,
score1,
score2,
playoff,
case when score1 > score2 then team1 else team2 end as winner,
case when score1 < score2 then team1 else team2 end as loser,
case when team1 = '${inputs.team1_dd.value}' then elo1_pre else elo2_pre end as elo,
case when team1 = '${inputs.team1_dd.value}' then score1 else score2 end as pf,
case when team1 = '${inputs.team1_dd.value}' then score2 else score1 end as pa,
'${inputs.team1_dd.value}' || ':' || '${inputs.team1_season_dd.value}' as key,
from ${elo_history } where (team1 = '${inputs.team1_dd.value}' OR team2 = '${inputs.team1_dd.value}') AND season = ${inputs.team1_season_dd.value}
)
select
key,
count(*) as ct,
count(*) filter (where winner = '${inputs.team1_dd.value}' and playoff = 'r') as wins,
-count(*) filter (where loser = '${inputs.team1_dd.value}' and playoff = 'r') as losses,
count(*) filter (where winner = '${inputs.team1_dd.value}' and team1 = '${inputs.team1_dd.value}' and playoff = 'r') as home_wins,
-count(*) filter (where loser = '${inputs.team1_dd.value}' and team1 = '${inputs.team1_dd.value}' and playoff = 'r') as home_losses,
count(*) filter (where winner = '${inputs.team1_dd.value}' and team2 = '${inputs.team1_dd.value}' and playoff = 'r') as away_wins,
-count(*) filter (where loser = '${inputs.team1_dd.value}' and team2 = '${inputs.team1_dd.value}' and playoff = 'r') as away_losses,
count(*) filter (where winner = '${inputs.team1_dd.value}' and playoff <> 'r') as playoff_wins,
-count(*) filter (where loser = '${inputs.team1_dd.value}' and playoff <> 'r') as playoff_losses,
avg(pf) as pf,
avg(-pa) as pa,
avg(pf) - avg(pa) as margin,
min(elo) as min_elo,
avg(elo) as avg_elo,
max(elo) as max_elo,
'${inputs.team1_dd.value}' as team,
${inputs.team1_season_dd.value} as season
from cte_games
GROUP BY ALL with cte_games AS (
select
team1,
team2,
score1,
score2,
playoff,
case when score1 > score2 then team1 else team2 end as winner,
case when score1 < score2 then team1 else team2 end as loser,
case when team1 = '${inputs.team2_dd.value}' then elo1_pre else elo2_pre end as elo,
case when team1 = '${inputs.team2_dd.value}' then score1 else score2 end as pf,
case when team1 = '${inputs.team2_dd.value}' then score2 else score1 end as pa,
'${inputs.team2_dd.value}' || ':' || '${inputs.team2_season_dd.value}' as key,
from ${elo_history } where (team1 = '${inputs.team2_dd.value}' OR team2 = '${inputs.team2_dd.value}') AND season = ${inputs.team2_season_dd.value}
)
select
key,
count(*) as ct,
count(*) filter (where winner = '${inputs.team2_dd.value}' and playoff = 'r') as wins,
-count(*) filter (where loser = '${inputs.team2_dd.value}' and playoff = 'r') as losses,
count(*) filter (where winner = '${inputs.team2_dd.value}' and team1 = '${inputs.team2_dd.value}' and playoff = 'r') as home_wins,
-count(*) filter (where loser = '${inputs.team2_dd.value}' and team1 = '${inputs.team2_dd.value}' and playoff = 'r') as home_losses,
count(*) filter (where winner = '${inputs.team2_dd.value}' and team2 = '${inputs.team2_dd.value}' and playoff = 'r') as away_wins,
-count(*) filter (where loser = '${inputs.team2_dd.value}' and team2 = '${inputs.team2_dd.value}' and playoff = 'r') as away_losses,
count(*) filter (where winner = '${inputs.team2_dd.value}' and playoff <> 'r') as playoff_wins,
-count(*) filter (where loser = '${inputs.team2_dd.value}' and playoff <> 'r') as playoff_losses,
avg(pf) as pf,
avg(-pa) as pa,
avg(pf) - avg(pa) as margin,
min(elo) as min_elo,
avg(elo) as avg_elo,
max(elo) as max_elo,
'${inputs.team2_dd.value}' as team,
${inputs.team2_season_dd.value} as season
from cte_games
GROUP BY ALL with cte_combined as (
select * from ${team1_stats}
union all
select * from ${team2_stats}
),
cte_unpivot as (
UNPIVOT cte_combined
ON COLUMNS(* EXCLUDE (key, ct, team, season))
INTO
NAME stat
VALUE value
),
cte_stats as (
select distinct stat
from cte_unpivot
)
select
CASE WHEN u1.value > u2.value THEN '✅' ELSE '' END AS "t1",
abs(u1.value::int) as "team1",
s.stat,
abs(u2.value::int) as "team2",
CASE WHEN u2.value > u1.value THEN '✅' ELSE '' END AS "t2"
from cte_stats s
left join cte_unpivot u1 on u1.stat = s.stat and u1.key = '${inputs.team1_dd.value}' || ':' || '${inputs.team1_season_dd.value}'
left join cte_unpivot u2 on u2.stat = s.stat and u2.key = '${inputs.team2_dd.value}' || ':' || '${inputs.team2_season_dd.value}' with cte_games AS (
select
date,
case when team1 = '${inputs.team1_dd.value}' then elo1_post else elo2_post end as elo,
'${inputs.team1_dd.value}' || ':' || '${inputs.team1_season_dd.value}' as key,
from ${elo_history } where (team1 = '${inputs.team1_dd.value}' OR team2 = '${inputs.team1_dd.value}') AND season = ${inputs.team1_season_dd.value}
)
select
key,
date,
elo,
'${inputs.team1_season_dd.value}' || ' ' || '${inputs.team1_dd.value}' as team,
ROW_NUMBER() OVER (ORDER BY date) as game_id
from cte_games with cte_games AS (
select
date,
case when team1 = '${inputs.team2_dd.value}' then elo1_post else elo2_post end as elo,
'${inputs.team2_dd.value}' || ':' || '${inputs.team2_season_dd.value}' as key,
from ${elo_history } where (team1 = '${inputs.team2_dd.value}' OR team2 = '${inputs.team2_dd.value}')
AND season = ${inputs.team2_season_dd.value}
)
select
key,
date,
elo,
'${inputs.team2_season_dd.value}' || ' ' || '${inputs.team2_dd.value}' as team,
ROW_NUMBER() OVER (ORDER BY date) as game_id
from cte_games select * from ${team1_trend}
union all
select * from ${team2_trend}<LineChart data={combined_trend} x=game_id y=elo title='elo change over time' series=team yMin={parseFloat(y_min)-25} xAxisTitle='games played' handleMissing=connect colorPalette={ [ '#29BDAD', '#DE4500' ] } />
This is a 10k iteration monte carlo sim, calculated in browser using DuckDB WASM.
select
t2.season || ' ' || t2.team as team2,
t2.avg_elo - ('${inputs.elo_slider}'::real/2) as elo2,
t1.season || ' ' || t1.team as team1,
t1.avg_elo + ('${inputs.elo_slider}'::real/2) as elo1
from ${team2_stats} t2
left join ${team1_stats} t1 ON 1=1 SELECT I.generate_series AS game_id
FROM generate_series(1, 7 ) AS I WITH cte_scenario_gen AS (
SELECT I.generate_series AS scenario_id
FROM generate_series(1, 10000 ) AS I
),
cte_schedule as (
SELECT
i.scenario_id,
G.game_id,
S.*,
(random() * 10000)::smallint AS rand_result
FROM cte_scenario_gen AS i
CROSS JOIN ${elo_by_team} AS S
LEFT JOIN ${games} G ON 1=1
),
cte_step_1 as (
Select *,
( 1 - (1 / (10 ^ (-( elo2 - elo1 )::real/400)+1))) * 10000 as team1_win_probability,
CASE
WHEN ( 1 - (1 / (10 ^ (-( elo2 - elo1 )::real/400)+1))) * 10000 >= rand_result THEN S.team1
ELSE S.team2
END AS winning_team,
From cte_schedule S
),
cte_step_2 AS (
SELECT step1.*,
ROW_NUMBER() OVER (PARTITION BY scenario_id, winning_team ORDER BY scenario_id, game_id ) AS series_result
FROM cte_step_1 step1
)
select * from cte_step_2 SELECT scenario_id,
game_id
FROM ${monte_carlo_sim}
WHERE series_result = 4with
cte_summary as (
SELECT step2.*
FROM ${monte_carlo_sim} step2
LEFT JOIN ${monte_carlo_winners} F ON F.scenario_id = step2.scenario_id
AND step2.game_id = f.game_id
)
SELECT
E.scenario_id,
E.game_id,
E.winning_team
FROM cte_summary E
where E.series_result = 4 select
winning_team,
game_id as games_played,
case when game_id = 4 then '4-0'
when game_id = 5 then '4-1'
when game_id = 6 then '4-2'
else '4-3'
end as result,
count(*) as occurances,
count(*) / 10000.0 as occurances_pct1
from ${mc_final_results}
group by all
order by resultIf you don't like the current results, you can modify the elo inputs with this slider.
The current value is {inputs.elo_slider}.