Team Span Stats Finder - Pro Basketball

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

main

Page Setup Time: 0.00202 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(pts) OVER w AS pts
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)
WHERE comp_id IN('NBA','BAA')
  AND year_id>=?
  AND year_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 pts 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) AS losses, SUM(win) / SUM(win + loss) as win_loss_pct_csk, TRIM(LEADING '0' FROM ROUND(SUM(win) / SUM(win + loss),3)) AS win_loss_pct, SUM(fg) AS fg, SUM(fga) AS fga, IF(SUM(fga), SUM((fga IS NOT NULL) * fg) / SUM(fga), NULL) as fg_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga), SUM((fga IS NOT NULL) * fg) / SUM(fga), NULL),3)) AS fg_pct, SUM(fg - IFNULL(fg3, 0)) AS fg2, SUM(fga - IFNULL(fg3a, 0)) AS fg2a, IF(SUM(fga - IFNULL(fg3a, 0)), SUM(((fga - IFNULL(fg3a, 0)) IS NOT NULL) * (fg - IFNULL(fg3, 0))) / SUM(fga - IFNULL(fg3a, 0)), NULL) as fg2_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga - IFNULL(fg3a, 0)), SUM(((fga - IFNULL(fg3a, 0)) IS NOT NULL) * (fg - IFNULL(fg3, 0))) / SUM(fga - IFNULL(fg3a, 0)), NULL),3)) AS fg2_pct, SUM(fg3) AS fg3, SUM(fg3a) AS fg3a, IF(SUM(fg3a), SUM((fg3a IS NOT NULL) * fg3) / SUM(fg3a), NULL) as fg3_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(fg3a), SUM((fg3a IS NOT NULL) * fg3) / SUM(fg3a), NULL),3)) AS fg3_pct, SUM(ft) AS ft, SUM(fta) AS fta, IF(SUM(fta), SUM(ft) / SUM(fta), NULL) as ft_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(fta), SUM(ft) / SUM(fta), NULL),3)) AS ft_pct, SUM(pts) AS pts, SUM(mp) AS mp, SUM(opp_fg) AS opp_fg, SUM(opp_fga) AS opp_fga, IF(SUM(opp_fga), SUM(opp_fg) / SUM(opp_fga), NULL) as opp_fg_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(opp_fga), SUM(opp_fg) / SUM(opp_fga), NULL),3)) AS opp_fg_pct, SUM(opp_fg - IFNULL(opp_fg3, 0)) AS opp_fg2, SUM(opp_fga - IFNULL(opp_fg3a, 0)) AS opp_fg2a, IF(SUM((opp_fga - IFNULL(opp_fg3a, 0))), SUM((opp_fg - IFNULL(opp_fg3, 0))) / SUM((opp_fga - IFNULL(opp_fg3a, 0))), NULL) as opp_fg2_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM((opp_fga - IFNULL(opp_fg3a, 0))), SUM((opp_fg - IFNULL(opp_fg3, 0))) / SUM((opp_fga - IFNULL(opp_fg3a, 0))), NULL),3)) AS opp_fg2_pct, SUM(opp_fg3) AS opp_fg3, SUM(opp_fg3a) AS opp_fg3a, IF(SUM(opp_fg3a), SUM(opp_fg3) / SUM(opp_fg3a), NULL) as opp_fg3_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(opp_fg3a), SUM(opp_fg3) / SUM(opp_fg3a), NULL),3)) AS opp_fg3_pct, SUM(opp_ft) AS opp_ft, SUM(opp_fta) AS opp_fta, IF(SUM(opp_fta), SUM(opp_ft) / SUM(opp_fta), NULL) as opp_ft_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(SUM(opp_fta), SUM(opp_ft) / SUM(opp_fta), NULL),3)) AS opp_ft_pct, SUM(opp_pts) AS opp_pts
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 IN('NBA','BAA')
  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 pts DESC, span_start_date_csk DESC, team_id 

SQL PARAMS -- $VAR1 = [
          2026,
          2026,
          'reg',
          5,
          'reg'
        ];

Query Time: 0.52 seconds
Table Build Time: 0.06 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'match' => 'team_span_game',
          'comp_id' => 'NBA',
          'year_min' => 2026,
          'order_by' => 'pts',
          'year_max' => 2026,
          'timeframe' => 'seasons',
          'span_length' => 5,
          'comp_type' => 'reg'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_max' => 2026,
          'year_min' => 2026,
          'comp_id' => 'NBA'
        };