Player Streak Stats Finder - Hockey

--------------------------------------

main

Page Setup Time: 0.00264 seconds

WITH
  Meta AS (
SELECT MAX(year_id) as MaxYear, person_id
FROM sup_player_team_seasons
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
WHERE type=? 
GROUP BY person_id 
  ),
  Gamelog AS (
SELECT person_id, year_id, team_id, (goals>=1) as met_criteria, LAG(goals>=1) OVER w AS lag_met_criteria, LEAD(goals>=1) OVER w AS lead_met_criteria, ROW_NUMBER() OVER w AS rn, date, player_game_num_career, phase_id, LAG(team_game_num_franchise)  OVER w AS lag_team_game_num_franchise, LEAD(team_game_num_franchise) OVER w AS lead_team_game_num_franchise, team_game_num_franchise
FROM sup_player_games
  JOIN sup_games USING (game_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, team_game_num_franchise, home_away_neutral, win, loss, tie FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_players USING (person_id)
WHERE type=?
  AND is_goalie = 0
  AND year_id>=?
  AND year_id<=?
  AND (forfeit IS NULL OR (forfeit != 'V' AND forfeit != 'H')) 
WINDOW w AS (PARTITION BY person_id ORDER BY date ) 
  ),
  StreakStart AS (
SELECT ROW_NUMBER() OVER w1 AS rownum, Gamelog.rn as rn_start, date AS streak_start_date, person_id, player_game_num_career AS gn_start
FROM Gamelog
WHERE met_criteria = 1
  AND (lag_met_criteria  = 0 OR lag_met_criteria  IS NULL OR ((team_game_num_franchise - lag_team_game_num_franchise) != 1) ) 
WINDOW w1 AS (PARTITION BY person_id ORDER BY date ) 
  ),
  StreakEnd AS (
SELECT ROW_NUMBER() OVER w2 AS rownum, Gamelog.rn as rn_end, date AS streak_end_date, IF(lead_met_criteria IS NULL AND year_id=MaxYear,1,0) AS active_streak, person_id, player_game_num_career AS gn_end
FROM Gamelog
  JOIN Meta USING (person_id)
WHERE met_criteria = 1
  AND (lead_met_criteria = 0 OR lead_met_criteria IS NULL OR ((lead_team_game_num_franchise - team_game_num_franchise) != 1) ) 
WINDOW w2 AS (PARTITION BY person_id ORDER BY date ) 
  ),
  streak_list AS (
SELECT person_id, streak_start_date, streak_end_date, (rn_end - rn_start)+1 as streak_length, active_streak
FROM StreakStart
  JOIN StreakEnd USING (person_id, rownum)
WHERE 1 
HAVING streak_length>=?
ORDER BY streak_length DESC, streak_end_date DESC LIMIT 0, 20
  )
SELECT name_display, name_display_csk, sup_players.link as name_display_link, IF(MIN(age_in_season)=MAX(age_in_season), MIN(age_in_season), CONCAT(MIN(age_in_season), '-', MAX(age_in_season))) as age_range, person_id, streak_length, streak_start_date as streak_start_date_csk, streak_start_date as streak_start_date, streak_end_date as streak_end_date_csk, streak_end_date as streak_end_date, active_streak as active_streak_csk, IF(active_streak=1,'*','') as active_streak, GROUP_CONCAT(DISTINCT UPPER(ts.name_abbr)) as teams_played_for_career, 'Games List' as details_games, SUM(goals IS NULL) as goals_incomplete, SUM(goals) AS goals, SUM(assists IS NULL) as assists_incomplete, SUM(assists) AS assists, SUM(points IS NULL) as points_incomplete, SUM(points) AS points, SUM(plus_minus IS NULL) as plus_minus_incomplete, SUM(plus_minus) AS plus_minus, SUM(pen_min IS NULL) as pen_min_incomplete, SUM(pen_min) AS pen_min, SUM(goals IS NULL)+SUM(goals_pp IS NULL)+SUM(goals_sh IS NULL) as goals_ev_incomplete, SUM(goals - (goals_pp + goals_sh)) AS goals_ev, SUM(goals_pp IS NULL) as goals_pp_incomplete, SUM(goals_pp) AS goals_pp, SUM(goals_sh IS NULL) as goals_sh_incomplete, SUM(goals_sh) AS goals_sh, SUM(goals_gw IS NULL) as goals_gw_incomplete, SUM(goals_gw) AS goals_gw, SUM(shots IS NULL) as shots_incomplete, SUM(shots) AS shots, SUM(time_on_ice)/60 as time_on_ice_csk, SUM(time_on_ice IS NULL) as time_on_ice_incomplete, IF(SUM(st.time_on_ice),CONCAT_WS(':', FLOOR(SUM(st.time_on_ice) / 60), LPAD(IF(ROUND(MOD(SUM(st.time_on_ice), 60)) >= 60, 0, ROUND(MOD(SUM(st.time_on_ice), 60))), 2, '0')),NULL) AS time_on_ice
FROM streak_list
  JOIN sup_player_games as st USING (person_id)
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie FROM sup_team_games) as tg USING (game_id, team_id)
  JOIN (SELECT person_id, year_id, age as age_in_season FROM sup_player_team_seasons GROUP BY person_id, year_id ) as ps2 USING (person_id, year_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  JOIN (SELECT team_id, year_id, name_abbr, name, link FROM sup_team_seasons GROUP BY team_id, year_id ) as ts USING (team_id, year_id)
WHERE type=?
  AND is_goalie = 0
  AND date>=streak_start_date
  AND date<=streak_end_date 
GROUP BY person_id, streak_start_date, streak_end_date
ORDER BY streak_length DESC, streak_end_date_csk DESC, person_id 

SQL PARAMS -- $VAR1 = [
          'reg',
          'reg',
          2025,
          2025,
          2,
          'reg'
        ];

Query Time: 1.21 seconds
Table Build Time: 0.04 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'streak_length' => 2,
          'timeframe' => 'seasons',
          'team_game_max' => 84,
          'comp_type' => 'reg',
          'pos' => 'S',
          'season_start' => 1,
          'team_game_min' => 1,
          'order_by' => 'streak_length',
          'order_by_asc' => '0',
          'match' => 'player_streak_game',
          'season_end' => -1,
          'rookie' => 'N',
          'year_min' => 2025,
          'year_max' => 2025
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'order_by' => 'streak_length',
          'year_min' => 2025,
          'year_max' => 2025
        };