Team Span Stats Finder - Hockey

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

main

Page Setup Time: 0.00123 seconds

WITH
  Gamelog AS (
SELECT year_id, team_id, FIRST_VALUE(date) OVER w as span_start_date_window, LAST_VALUE(date) OVER w as span_end_date_window, ROW_NUMBER() OVER w AS rn, COUNT(*) OVER w AS played_in_span, date, team_game_num_franchise, phase_id, SUM(goals) OVER w AS goals
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_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_comp_seasons USING (comp_id, year_id)
WHERE year_id>=?
  AND year_id<=?
  AND comp_id=?
  AND type=?
  AND forfeit IS NULL
  AND date < current_date() 
WINDOW w AS (PARTITION BY team_id ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
  ),
  span_list AS (
SELECT team_id, played_in_span, span_start_date_window as span_start_date, span_end_date_window as span_end_date
FROM Gamelog
WHERE played_in_span=? 
ORDER BY goals DESC, span_start_date DESC LIMIT 0, 20
  )
SELECT ts.name_abbr as team_name_abbr, ts.link as team_link, team_id, span_start_date as span_start_date_csk, span_start_date as span_start_date, span_end_date as span_end_date_csk, span_end_date as span_end_date, 'Games List' as details_games, COUNT(*) AS games, SUM(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1) AS ties, SUM(year_id>=2000 AND type="reg" AND loss=1 AND overtimes>0) AS losses_ot, SUM(goals) AS goals, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(shots) AS shots, SUM(pen_min) AS pen_min, SUM(goals_against) AS goals_against, SUM(goals_against_pp) AS goals_against_pp, SUM(goals_against_sh) AS goals_against_sh, SUM(shots_against) AS shots_against, SUM(opp_pen_min) AS opp_pen_min, SUM(chances_pp) AS chances_pp, SUM(opp_chances_pp) AS opp_chances_pp, SUM(goals) - SUM(goals_against) AS score_differential
FROM span_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 comp_id=?
  AND type=?
  AND date>=span_start_date
  AND date<=span_end_date
  AND forfeit IS NULL 
GROUP BY team_id, span_start_date, span_end_date
ORDER BY goals DESC, span_start_date_csk DESC, team_id 

SQL PARAMS -- $VAR1 = [
          1918,
          2025,
          'NHL',
          'reg',
          5,
          'NHL',
          'reg'
        ];

Query Time: 2.60 seconds
Table Build Time: 0.04 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'timeframe' => 'seasons',
          'year_min' => 1918,
          'year_max' => 2025,
          'order_by' => 'goals',
          'span_length' => 5,
          'match' => 'team_span_game',
          'comp_id' => 'NHL',
          'comp_type' => 'reg'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'comp_id' => 'NHL',
          'year_max' => 2025,
          'year_min' => 1918
        };