Player Span Stats Finder - Pro Basketball

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

main

Page Setup Time: 0.00209 seconds

WITH
  Gamelog AS (
SELECT person_id, 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, phase_id, SUM(pts) OVER w AS pts, COUNT(*) OVER w AS games
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, 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)
WHERE comp_id IN('NBA','BAA')
  AND type=?
  AND year_id<=?
  AND year_id>=?
  AND (forfeit IS NULL OR (forfeit != 'V' AND forfeit != 'H'))
  AND date < current_date() 
WINDOW w AS (PARTITION BY person_id ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
  ),
  span_list AS (
SELECT person_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=? 
GROUP BY person_id, span_start_date
ORDER BY pts DESC, span_start_date DESC LIMIT 0, 20
  )
SELECT name_display, name_display_csk, sup_players.link as name_display_link, person_id, MIN(age_on_day) as age_span_start, 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, GROUP_CONCAT(DISTINCT UPPER(ts.name_abbr)) as teams_played_for_career, 'Games List' as details_games, YEAR(span_start_date) as year_min, COUNT(*) AS games, SUM(is_starter) as is_starter_csk, SUM(is_starter IS NULL) as is_starter_incomplete, SUM(is_starter) AS is_starter, SUM(mp IS NULL) as mp_incomplete, SUM(mp) AS mp, SUM(fg IS NULL) as fg_incomplete, SUM(fg) AS fg, SUM(fga IS NULL) as fga_incomplete, SUM(fga) AS fga, IF(SUM(fga), SUM((fga IS NOT NULL) * fg) / SUM(fga), NULL) as fg_pct_csk, SUM(fga IS NULL)+SUM(fg IS NULL) as fg_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga), SUM((fga IS NOT NULL) * fg) / SUM(fga), NULL),3)) AS fg_pct, NULL as fg2_incomplete, SUM(fg - IFNULL(fg3, 0)) AS fg2, NULL as fg2a_incomplete, 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, NULL as fg2_pct_incomplete, 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, NULL as fg3_incomplete, SUM(fg3) AS fg3, NULL as fg3a_incomplete, SUM(fg3a) AS fg3a, IF(SUM(fg3a), SUM((fg3a IS NOT NULL) * fg3) / SUM(fg3a), NULL) as fg3_pct_csk, NULL as fg3_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fg3a), SUM((fg3a IS NOT NULL) * fg3) / SUM(fg3a), NULL),3)) AS fg3_pct, SUM(ft IS NULL) as ft_incomplete, SUM(ft) AS ft, SUM(fta IS NULL) as fta_incomplete, SUM(fta) AS fta, IF(SUM(fta), SUM(ft) / SUM(fta), NULL) as ft_pct_csk, SUM(fta IS NULL)+SUM(ft IS NULL) as ft_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fta), SUM(ft) / SUM(fta), NULL),3)) AS ft_pct, IF(SUM(fga + fta), SUM((fga IS NOT NULL AND fta IS NOT NULL) * pts) / SUM(2 * (fga + (0.44 * fta))), NULL) as ts_pct_csk, SUM(pts IS NULL)+SUM(fta IS NULL)+SUM(fga IS NULL) as ts_pct_incomplete, TRIM(LEADING '0' FROM ROUND(IF(SUM(fga + fta), SUM((fga IS NOT NULL AND fta IS NOT NULL) * pts) / SUM(2 * (fga + (0.44 * fta))), NULL),3)) AS ts_pct, SUM(orb IS NULL) as orb_incomplete, SUM(orb) AS orb, SUM(orb IS NULL)+SUM(trb IS NULL) as drb_incomplete, SUM(trb - orb) AS drb, SUM(trb IS NULL) as trb_incomplete, SUM(trb) AS trb, SUM(ast IS NULL) as ast_incomplete, SUM(ast) AS ast, SUM(stl IS NULL) as stl_incomplete, SUM(stl) AS stl, SUM(blk IS NULL) as blk_incomplete, SUM(blk) AS blk, SUM(((pts>=10)+(trb IS NOT NULL AND trb>=10)+(blk IS NOT NULL AND blk>=10)+(stl IS NOT NULL AND stl>=10)+(ast IS NOT NULL AND ast>=10))>=3) AS tpl_dbl, SUM(((pts>=10)+(trb IS NOT NULL AND trb>=10)+(blk IS NOT NULL AND blk>=10)+(stl IS NOT NULL AND stl>=10)+(ast IS NOT NULL AND ast>=10))>=2) AS dbl_dbl, SUM(tov IS NULL) as tov_incomplete, SUM(tov) AS tov, SUM(pf IS NULL) as pf_incomplete, SUM(pf) AS pf, SUM(pts IS NULL) as pts_incomplete, SUM(pts) AS pts, SUM(pts + 0.4 * fg - 0.7 * fga - 0.4 * (fta - ft) + 0.7 * orb + 0.3 * (trb - orb) + stl + 0.7 * ast + 0.7 * blk - 0.4 * pf - tov) / COUNT(*) as game_score_csk, SUM(trb IS NULL)+SUM(fga IS NULL)+SUM(pf IS NULL)+SUM(blk IS NULL)+SUM(fg IS NULL)+SUM(stl IS NULL)+SUM(pts IS NULL)+SUM(orb IS NULL)+SUM(tov IS NULL)+SUM(ft IS NULL)+SUM(ast IS NULL)+SUM(fta IS NULL) as game_score_incomplete, ROUND(SUM(pts + 0.4 * fg - 0.7 * fga - 0.4 * (fta - ft) + 0.7 * orb + 0.3 * (trb - orb) + stl + 0.7 * ast + 0.7 * blk - 0.4 * pf - tov) / COUNT(*),1) AS game_score, SUM(plus_minus IS NULL) as plus_minus_incomplete, SUM(plus_minus) AS plus_minus, SUM(bpm * time_on_court) / SUM(time_on_court) as bpm_csk, SUM(time_on_court IS NULL)+SUM(bpm IS NULL) as bpm_incomplete, ROUND(SUM(bpm * time_on_court) / SUM(time_on_court),1) AS bpm
FROM span_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, comp_id, phase_id, team_id FROM sup_player_team_seasons) as ps2 USING (person_id, year_id, comp_id, phase_id, 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, comp_id, phase_id FROM sup_team_seasons) as ts USING (team_id, year_id, comp_id, phase_id)
WHERE comp_id IN('NBA','BAA')
  AND type=?
  AND date>=span_start_date
  AND date<=span_end_date 
GROUP BY person_id, span_start_date, span_end_date
ORDER BY pts DESC, span_start_date_csk DESC, person_id 

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

Query Time: 0.33 seconds
Table Build Time: 0.06 seconds


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