Pitching Streak Finder — Harry Patton

set @count:=0; set @PYEAR:=0; set @GCOUNT:=0; set @STLEN := 0; set @previous := NULL; SET @ENTLEN:=0;
select /* br:streak_finder:1 */ t.result_pitcher, concat('/players/',left(t.result_pitcher,1),'/', t.result_pitcher, '.shtml') as player_link,
   min(t.entity_len) AS entity_check_start,
   date_format(max((t.streak=1)*t.date_game),"%Y-%m-%d") as streak_start,
   MAX(             t.date_game) as streak_end,
   rpad(max((t.streak=1)*concat(t.year_game, '.' , lpad(t.pitching_team_game,3,"0"))),8,"0") as first_tgame,
   rpad(MAX(             concat(t.year_game, '.' , lpad(t.pitching_team_game,3,"0"))),8,"0") as last_tgame,
   MAX((t.streak=1)*pitcher_game) as player_game_min,
   MAX(pitcher_game)     as player_game_max,
   max(gcount)           as player_gcount_max,
   max(t.streak) as Games_streak,
   min(t.streak) as min_Games_streak,
   group_concat(pitcher_game*(t.streak > 0) order by pitcher_game ASC separator ',') as gstreak,
     birth_year, birth_month, birth_day, death_year,
  if (birth_country = 'Canada' OR birth_country='United States',concat(birth_city,', ',birth_state),if(birth_city is null, birth_country, concat(birth_city,', ',birth_country))) as birth_location,
  if (death_country = 'Canada' OR death_country='United States',concat(death_city,', ',death_state),if(birth_city is null, death_country, concat(death_city,', ',death_country))) as death_location

from (
select x.result_pitcher, x.pitcher_game, x.pitching_team_game, x.year_game, x.date_game,    @previous,
@STAT := x.stat,
if((x.result_pitcher = @previous), @GCOUNT := @GCOUNT + 1, @GCOUNT := 1) as gcount,
if((@STAT >= 1) && (x.result_pitcher = @previous), @STLEN := @STLEN + 1,
if( @STAT >= 1, @STLEN := 1, @STLEN := 0)) as streak,

if((x.result_pitcher = @previous),     @ENTLEN := @ENTLEN + 1,      @ENTLEN := 0) as entity_len,


if((@STAT >= 1) && (x.result_pitcher = @previous), @count, @count := @count + 1) as counter,
@previous := x.result_pitcher,
@PYEAR    := x.year_game,
birth_country, birth_state, birth_city, birth_year, birth_month, birth_day,
death_country, death_state, death_city, death_year, death_month, death_day
from
(
select pitcher_game, A.year_game, pitching_team_game, A.date_game,  result_pitcher,
(A.W = 1 AND A.L = 0) as stat,
birth_country, birth_state, birth_city, birth_year, birth_month, birth_day,
death_country, death_state, death_city, death_year, death_month, death_day
from pgl as A  inner join tgl_stats as tgl_stats on (A.game_id=tgl_stats.game_id and A.pitching_team_id=tgl_stats.team_id)
              inner join players on (A.result_pitcher=players.player_ID)
              inner join bio     on (A.result_pitcher=bio.player_ID)
              
 
where A.year_game = 2020 AND A.result_pitcher = 'pattoha01' 
order by result_pitcher, pitcher_game ASC) as x

) as t
group by counter
having Games_streak > 0 
order by Games_streak desc, streak_end desc limit 0,1000