Play Finder

/* mysql8 */
  select /* pbp_play_plays */ *,
  pbp_play_new.sr_description as description,
  pbp_play_new.score_points as points,
  ifnull(minutes,if(seconds is not null,0,null)) as minutes,
  concat(upper(field),' ',yardline) as location
  from pbp_play_new 
  left join pbp_play_stats using (game_id, play_uuid)
  right join game_master using (game_id)
  right join games_team on (games_team.game_id = pbp_play_new.game_id and games_team.team_id = poss_team)
  where poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1 
  group by play_uuid
  order by yards desc 
  limit 20


$VAR1 = [
          'kan',
          2019
        ];
/* mysql8 */
WITH playsum as (
SELECT /* pbp_play_play_sum */
  COUNT(DISTINCT(pbp_play_new.game_id)) as games,
  SUM(IF(play_type != 'kickoff',yards,0)) as yards,
  (play_type='pass') as pass,
  (play_type='rush') as rush,
  (play_type='field_goal') as fg,
  (play_type='kickoff') as koff,
  (play_type='conversion') as 2pcr,
  (play_type='extra_point') as xp,
  (play_type='punt') as punt, 
  (play_type='onside_kick') as onsd,
  (down=1) as first_down,
  (down=2) as second_down,
  (down=3) as third_down,
  (down=4) as fourth_down,
  (down IS NULL) as no_down,
  distance,
  (distance>0 && distance<4) as ytg_0_3,
  (distance>3 && distance<7) as ytg_4_6, 
  (distance>6 && distance<11) as ytg_7_10,
  (distance>=11) as ytg_11,
  (score_type = 'touchdown') as touchdowns,
  (score_type = 'field_goal') as field_goals,
  (is_interception) as interceptions,
  (is_fumble) as fumbles,
  (is_sack) as sacks,
  (is_first_down) as first_downs
FROM pbp_play_new
  LEFT JOIN pbp_play_stats using (game_id, play_uuid)
  RIGHT
  JOIN game_master USING (game_id)
  RIGHT
  JOIN games_team ON (games_team.game_id = pbp_play_new.game_id AND games_team.team_id = poss_team)
WHERE poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1 
GROUP BY play_uuid
  )
SELECT 
  SUM(games) as games,
  COUNT(*) as plays,
  SUM(yards) as yards,
  SUM(pass) as pass,
  SUM(rush) as rush,
  SUM(fg) as fg,
  SUM(koff) as koff,
  SUM(2pcr) as 2pcr,
  SUM(xp) as xp,
  SUM(punt) as punt, 
  SUM(onsd) as onsd,
  SUM(first_down) as first_down,
  SUM(second_down) as second_down,
  SUM(third_down) as third_down,
  SUM(fourth_down) as fourth_down,
  SUM(no_down) as no_down,
  ROUND(AVG(distance),1) as avg_yds_to_go,
  SUM(ytg_0_3) as ytg_0_3,
  SUM(ytg_4_6) as ytg_4_6, 
  SUM(ytg_7_10) as ytg_7_10,
  SUM(ytg_11) as ytg_11,
  SUM(touchdowns) as touchdowns,
  SUM(field_goals) as field_goals,
  SUM(interceptions) as interceptions,
  SUM(fumbles) as fumbles,
  SUM(sacks) as sacks,
  SUM(first_downs) as first_downs
FROM playsum


$VAR1 = [
          'kan',
          2019
        ];
/* mysql8 */
  select games_team.team_id as team_id,
  sum(if(play_type='pass',1,0)) as pass,
  sum(if(play_type='rush',1,0)) as rush,
  sum(if(play_type='field_goal',1,0)) as pt_fg,
  sum(if(play_type='punt',1,0)) as pt_punt,
  pbp_play_new.year_id,
  count(distinct(pbp_play_new.game_id)) as g,
  count(*) as plays,
  round(avg(yards),1) as avg_yds,
  round(avg(distance),1) as avg_yds_to_go,
  sum(is_first_down) as first_down,
  round(100*sum(is_first_down)/count(*),1) as first_down_pct,
  round(100*sum(is_turnover)/count(*),1) as to_pct,
  sum(if(score_type = 'touchdown' and scoring_team_id = poss_team,1,0)) as touchdowns,
  sum(if(score_type = 'field_goal' and scoring_team_id = poss_team,1,0)) as field_goals,
  sum(is_interception) as interceptions,
  sum(is_fumble) as fumbles,
  sum(is_sack) as sacks
  from pbp_play_new
  inner join game_master using (game_id)
  inner join games_team on (pbp_play_new.game_id = games_team.game_id and pbp_play_new.poss_team = games_team.team_id )
  where poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1 
  and ifnull(is_timeout,0) != 1 
  group by games_team.team_id


$VAR1 = [
          'kan',
          2019
        ];
/* mysql8 */
  select games_team.opp_id as team_id,
  sum(if(play_type='pass',1,0)) as pass,
  sum(if(play_type='rush',1,0)) as rush,
  sum(if(play_type='field_goal',1,0)) as pt_fg,
  sum(if(play_type='punt',1,0)) as pt_punt,
  pbp_play_new.year_id,
  count(distinct(pbp_play_new.game_id)) as g,
  count(*) as plays,
  round(avg(yards),1) as avg_yds,
  round(avg(distance),1) as avg_yds_to_go,
  sum(is_first_down) as first_down,
  round(100*sum(is_first_down)/count(*),1) as first_down_pct,
  round(100*sum(is_turnover)/count(*),1) as to_pct,
  sum(if(score_type = 'touchdown' and scoring_team_id = poss_team,1,0)) as touchdowns,
  sum(if(score_type = 'field_goal' and scoring_team_id = poss_team,1,0)) as field_goals,
  sum(is_interception) as interceptions,
  sum(is_fumble) as fumbles,
  sum(is_sack) as sacks
  from pbp_play_new
  inner join game_master using (game_id)
  inner join games_team on (pbp_play_new.game_id = games_team.game_id and pbp_play_new.poss_team = games_team.team_id )
  where poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1 
  and ifnull(is_timeout,0) != 1 
  group by games_team.opp_id


$VAR1 = [
          'kan',
          2019
        ];
/* mysql8 */
  select /* pbp_play_pass_sum */ player_id, 
  CONCAT_WS(" ",name_first, name_last,name_suffix) as player,
  CONCAT_WS("-",name_last, name_first) as player_csk,
  sum(pbp_play_stats.pass_att) as pass_att,
  sum(pbp_play_stats.pass_cmp) as pass_cmp, 
  sum(pbp_play_stats.pass_td) as pass_td,
  sum(pbp_play_stats.pass_int) as pass_int,
  sum(pbp_play_stats.pass_yds) as pass_yds,
  sum(pbp_play_stats.pass_sacked) as pass_sacked,
  sum(pbp_play_stats.pass_sacked_yds) as pass_sacked_yds,
  sum(pbp_play_stats.pass_first_down) as first_down_pass,
  sum(pbp_play_stats.targets) as targets,
  sum(pbp_play_stats.rec) as rec,
  round(100 * sum(pbp_play_stats.rec)/sum(pbp_play_stats.targets),2) as catch_pct,
  sum(pbp_play_stats.rec_td) as rec_td,
  sum(pbp_play_stats.rec_yds) as rec_yds,
  sum(pbp_play_stats.rec_first_down) as first_down_rec,
  sum(pbp_play_stats.rush_att) as rush_att,
  sum(pbp_play_stats.rush_td) as rush_td,
  sum(pbp_play_stats.rush_yds) as rush_yds,
  sum(pbp_play_stats.rush_first_down) as first_down_rush
  from pbp_play_stats
  JOIN master USING (player_id)
  left join pbp_play_new using (game_id, play_uuid)
  right join game_master using (game_id)
  right join games_team on (games_team.game_id = pbp_play_new.game_id and games_team.team_id = pbp_play_new.poss_team)
  where poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1
  group by player_id
  having (ifnull(pass_att,0) + ifnull(rush_att,0) + ifnull(targets,0)) > 0
  order by pass_att desc, rec desc, rush_att desc


$VAR1 = [
          'kan',
          2019
        ];
/* mysql8 */
SELECT /* pbp_play_fg_sum */ player_id, 
  CONCAT_WS(" ",name_first, name_last,name_suffix) as player,
  CONCAT_WS("-",name_last, name_first) as player_csk,
  sum(s.fgm) as fgm,
  sum(s.fga) as fga,
  sum(s.xpm) as xpm,
  sum(s.xpa) as xpa,
  sum(s.punt) as punt,
  sum(s.punt_yds) as punt_yds,
  max(s.punt_yds) as punt_long,
  round(sum(s.punt_yds)/sum(s.punt),1) as punt_yds_per_punt,
  sum(s.punt_ret) as punt_ret,
  sum(s.punt_ret_yds) as punt_ret_yds,
  sum(s.punt_ret_td) as punt_ret_td,
  max(s.punt_ret_yds) as punt_ret_long,
  sum(s.kickoff) as kicks,
  sum(s.kickoff_tb) as kick_touchbacks,
  sum((select sum(ifnull(kick_ret_yds,0)) from pbp_play_stats j where j.play_uuid = s.play_uuid)) as kick_ret_yds_opp,
  sum((select sum(ifnull(kick_ret_td,0)) from pbp_play_stats j where j.play_uuid = s.play_uuid)) as kick_ret_td_opp,
  sum(s.kickoff_onside) as onside,
  sum(s.kickoff_onside_success) as onside_success,
  round(sum(s.punt_ret_yds)/sum(s.punt_ret),1) as punt_ret_yds_per_ret
FROM pbp_play_stats s
  JOIN master USING (player_id)
  LEFT JOIN pbp_play_new USING (game_id, play_uuid)
  RIGHT
  JOIN game_master USING (game_id)
  RIGHT
  JOIN games_team ON (games_team.game_id = pbp_play_new.game_id and games_team.team_id = pbp_play_new.poss_team)
WHERE poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1 
GROUP BY player_id
HAVING (ifnull(fga,0) + ifnull(xpa,0) + ifnull(punt,0) + ifnull(punt_ret,0) + ifnull(kicks,0)) > 0
ORDER BY fga desc, xpa desc, punt desc, punt_ret DESC


$VAR1 = [
          'kan',
          2019
        ];
/* mysql8 */
  select /* pbp_play_def_sum */ player_id,
  CONCAT_WS(" ",name_first, name_last,name_suffix) as player,
  CONCAT_WS("-",name_last, name_first) as player_csk,
  sum(s.sacks) as sacks,
  sum(s.tackles_solo) as tackles_solo,
  sum(s.tackles_assists) as tackles_assists,
  sum(ifnull(s.tackles_assists,0)+ifnull(s.tackles_solo,0)) as tackles_combined,
  sum(s.pass_defended) as pass_defended,
  sum(s.def_int) as def_int,
  sum(s.def_int_yds) as def_int_yds,
  sum(s.def_int_td) as def_int_td
FROM pbp_play_stats s
  JOIN master USING (player_id)
  left join pbp_play_new using (game_id, play_uuid)
  right join game_master using (game_id)
  right join games_team on (games_team.game_id = pbp_play_new.game_id and games_team.team_id = pbp_play_new.poss_team)
  where poss_team = ? and pbp_play_new.year_id = ? and games_team.is_playoff_game != 1 
  group by player_id
  having (ifnull(tackles_solo,0) + ifnull(tackles_assists,0) + ifnull(sacks,0) + ifnull(def_int,0)) > 0
  order by def_int desc, sacks desc, tackles_combined desc


$VAR1 = [
          'kan',
          2019
        ];