sdio_ats.py

#ats calc

#params
project_id = 'sportsdataio'
table_name = 'nfl.sdio_ats'

#query
sql_expr = """

-- select historical consensus spreads for every week of nfl season 

with a as 
(SELECT 
a.ScoreId, 
a.HomePointSpread, 
a.AwayPointSpread,
a.OverUnder
FROM nfl.sdio_gameoddsbyweekinnertotal as a
where a.Sportsbook = 'Consensus'),

-- select historical season scores for every week of nfl season 
b as 
(
select * from nfl.sdio_seasonscores
),

-- merge weekly point spreads and weekly scores for comparison 

c as (
select 
a.*,
b.HomeScore,
b.AwayScore,
b.HomeScore+b.AwayScore as TotalScore,
b.HomeTeam, 
b.AwayTeam
from a
left join b on a.ScoreId = b.ScoreID
),

-- create actual point spread for home/away teams

d as (
select
c.*, 
c.AwayScore - c.HomeScore as home_spread_act,
c.HomeScore - c.AwayScore as away_spread_act

from c
),

-- create field that evaluates if the home/away team covered against the spread (ats)

e as (
select
d.*, 
case when d.home_spread_act < d.HomePointSpread then 1 else 0 end as home_cover,
case when d.away_spread_act < d.AwayPointSpread then 1 else 0 end as away_cover
from d ),

-- aggregate home_ats by team 

e1 as (
select
e.HomeTeam,
sum(e.home_cover) as sum
from e 
group by 1),

-- aggregate away_ats by team 

e2 as (
select
e.AwayTeam,
sum(e.away_cover) as sum
from e 
group by 1),

-- select all games from the season 

f as (
select a.Team,a.Games from nfl.TeamSeasonStats as a
),

-- merge all games with aggregate ats results
g as (
select 
f.Team,
f.Games,
e1.sum as homecover,
e2.sum as awaycover
from f
left join e1 on f.Team = e1.HomeTeam
left join e2 on f.Team = e2.AwayTeam
),

-- create ats_win and ats_loss 
h as (
select 
g.*,
(g.homecover+g.awaycover) as ats_win,
g.Games-(g.homecover+g.awaycover) as ats_loss
from g 
)

select * from h
"""

sql_df = pandas_gbq.read_gbq(sql_expr, project_id=project_id)

pd_gbq.to_gbq(sql_df, table_name, project_id=project_id, if_exists='replace')