Team Pitching Streak Stats Finder - Baseball

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

main

Page Setup Time: 0.00170 seconds

WITH
  Meta AS (
SELECT MAX(year_id) as MaxYear, team_id
FROM sup_team_seasons
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
WHERE sup_phase_seasons.type=? 
GROUP BY team_id 
  ),
  Gamelog AS (
SELECT year_id, team_id, (win=1) as met_criteria, LAG(win=1) OVER w AS lag_met_criteria, LEAD(win=1) OVER w AS lead_met_criteria, ROW_NUMBER() OVER w AS rn, CONCAT(`date`,IF(date_game_number,CONCAT(" (",date_game_number,")"),"")) as date, team_game_num_franchise, phase_id
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
WHERE sup_phase_seasons.type=?
  AND year_id>=?
  AND forfeit IS NULL
  AND date<current_date() 
WINDOW w AS (PARTITION BY team_id ORDER BY date, date_game_number ) 
  ),
  StreakStart AS (
SELECT ROW_NUMBER() OVER w1 AS rownum, Gamelog.rn as rn_start, date AS streak_start_date, team_id
FROM Gamelog
WHERE met_criteria = 1
  AND (lag_met_criteria = 0 OR lag_met_criteria IS NULL) 
WINDOW w1 AS (PARTITION BY team_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, team_id
FROM Gamelog
  JOIN Meta USING (team_id)
WHERE met_criteria = 1
  AND (lead_met_criteria = 0 OR lead_met_criteria IS NULL) 
WINDOW w2 AS (PARTITION BY team_id ORDER BY date ) 
  ),
  streak_list AS (
SELECT team_id, streak_start_date, streak_end_date, (rn_end - rn_start)+1 as streak_length, active_streak
FROM StreakStart
  JOIN StreakEnd USING (team_id, rownum)
WHERE 1 
HAVING streak_length>=?
ORDER BY streak_length DESC, streak_end_date DESC LIMIT 0, 20
  )
SELECT ts.name_abbr as team_name_abbr, ts.link as team_link, team_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, 'Games List' as details_games, COUNT(*) AS b_games, SUM(win=1) AS wins, SUM(loss=1) AS losses, SUM(win)/(SUM(win+loss)) as team_win_loss_perc_csk, TRIM(LEADING '0' FROM ROUND(SUM(win)/(SUM(win+loss)),3)) AS team_win_loss_perc, SUM(p_ip_outs) as p_ip_csk, SUM(p_ip_outs) DIV 3 + 0.1 * MOD(SUM(p_ip_outs),3) AS p_ip, SUM(p_h) AS p_h, SUM(p_r) AS p_r, SUM(p_er) AS p_er, SUM(IF(p_er IS NULL, 0, p_r - p_er)) AS p_uer, SUM(p_hr) AS p_hr, SUM(p_bb) AS p_bb, SUM(p_ibb) AS p_ibb, SUM(p_so) AS p_so, SUM(p_hbp) AS p_hbp, SUM(p_bk) AS p_bk, SUM(p_wp) AS p_wp, SUM(p_bfp) AS p_bfp, SUM(p_h + IFNULL(p_bb,0) + IFNULL(p_hbp,0) + IFNULL(p_roe,0) + IFNULL(p_reached_on_strikeout,0)) AS p_baserunners, SUM(p_s) AS p_s, SUM(p_cg) AS p_cg, SUM(p_sho) AS p_sho
FROM streak_list
  JOIN sup_team_games as st USING (team_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)
  JOIN sup_games USING (game_id)
WHERE sup_phase_seasons.type=?
  AND CONCAT(`date`,IF(date_game_number,CONCAT(" (",date_game_number,")"),""))>=streak_start_date
  AND CONCAT(`date`,IF(date_game_number,CONCAT(" (",date_game_number,")"),""))<=streak_end_date 
GROUP BY team_id, streak_start_date, streak_end_date
ORDER BY streak_length DESC, streak_end_date_csk DESC, team_id 

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

Query Time: 2.64 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'order_by_asc' => '0',
          'match' => 'team_streak_game',
          'order_by' => 'streak_length',
          'team_game_min' => 1,
          'max_wind_speed' => 90,
          'max_temperature' => 120,
          'streak_length' => 2,
          'year_max' => 2025,
          'timeframe' => 'seasons',
          'comp_type' => 'reg',
          'year_min' => 2025,
          'team_game_max' => 165
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_max' => 2025,
          'order_by' => 'streak_length',
          'order_by_asc' => '0',
          'year_min' => 2025
        };