create table pitcher_games as
select pitch_id as pitcher,events.game_id,substring(events.game_id,4,8) as date,
events.fielding_team as team,bat_team as opp,pitch_hand as hand,
sum(batter_event='T') as pa,sum(event_type=3) as k,
sum(event_type=14) as unint_bb,sum(event_type=15) as int_bb,
sum(event_type=16) as hbp,sum(event_type=23) as hr,
sum(event_type=20) as b1,sum(event_type=21) as b2,sum(event_type=22) as b3,
sum(event_type=4) as sb,sum(event_type=5) as def_ind,
sum(event_type=6) as cs,sum(event_type=8) as po,sum(event_type=9) as wp,
sum(event_type=10) as pb,sum(event_type=11) as bk,
sum(event_type=17) as catch_int,
sum(ab='T') as ab,sum(sh='T') as sh,sum(sf='T') as sf,
sum(outs_on_play) as outs,sum(double_play='T') as gidp,
sum(triple_play='T') as gitp,sum(battedball_type='G') as gb,
sum(battedball_type='F') as fb,sum(battedball_type='L') as ld,
sum(battedball_type='P') as pop,sum(errors) as errs,
sum(pa_balls-pa_int_balls-pa_pitchout_balls) as balls_unint,
sum(pa_int_balls+pa_pitchout_balls) as balls_int,
sum(pa_strikes) as strikes,sum(pa_called_strikes) as called,
sum(pa_swingmiss_strikes) as swingmiss,
sum(pa_foul_strikes) as foul_strike,sum(batter_fate>3) as runs,
sum(batter_fate>4) as Unearned,LI,
pitch_id=gamelogs.vis_starter or pitch_id=gamelogs.home_starter as starts,
pitch_id=gamelogs.winner as win,pitch_id=gamelogs.loser as loss,
pitch_id=gamelogs.save as save,
(bat_team_homeaway=0 and gamelogs.home_score>gamelogs.vis_score) or
(bat_team_homeaway=1 and gamelogs.vis_score>gamelogs.home_score) as team_win,
(bat_team_homeaway=0 and gamelogs.home_score<gamelogs.vis_score) or
(bat_team_homeaway=1 and gamelogs.vis_score<gamelogs.home_score) as team_loss,
park_id as park
from events
inner join gamelogs
on events.game_id=gamelogs.game_id
inner join leverage
on events.game_id=leverage.game_id and events.pitch_id=leverage.pitcher
group by pitcher,events.game_id
order by date DESC,events.game_id DESC;
burrried.