bball-query.sql

select
    gameid
    , s as seconds_remaining
into tmp_game_states
from (select distinct gameid from ncaa_basketball) b
    , generate_series(0, 2400);

select
    g.gameid
    , g.seconds_remaining
    , bb.away
    , bb.home
    , max(case when g.seconds_remaining = 2400 then 0 else bb.away_score end) as away_score
    , max(case when g.seconds_remaining = 2400 then 0 else bb.home_score end) as home_score
from
    tmp_game_states g
left join
    ncaa_basketball bb
        on g.seconds_remaining = bb.seconds_remaining
        and g.gameid = bb.gameid
group by
    g.gameid
    , g.seconds_remaining
    , bb.away
    , bb.home
order by
    g.gameid
    , g.seconds_remaining desc