runs_to_end_of_inning.sql

with

rs as (
	select
		game_pk
		, event_inning
		, top_inning_sw
		, max(bat_score) as bat_score_end_of_inning
		, sum(event_outs) as event_outs
		
	from
		onerow_per_pa
		
	where year >= 2010
		
	group by
		game_pk
		, event_inning
		, top_inning_sw
)

select
	pa.year
	, pa.runners_on_cd
	, pa.outs
	, sum(1) as N
	, sum(bat_score_end_of_inning - bat_score)/sum(1.0) as runs_to_end_of_inning
	, sum(case when bat_score_end_of_inning = bat_score then 0 else 1 end)/sum(1.0) as runs_chance_of_scoring
	
from
	onerow_per_pa pa
	inner join
	rs
		on pa.game_pk = rs.game_pk
		and pa.event_inning = rs.event_inning
		and pa.top_inning_sw = rs.top_inning_sw

where rs.event_outs = 3
and rs.event_inning <= 8
		
group by
	pa.year
	, pa.runners_on_cd
	, pa.outs
;