Team Span Stats Finder - Pro Football

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

main

Page Setup Time: 0.00295 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(pass_td) OVER w AS pass_td
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 type="reg"
  AND year_id>=?
  AND year_id<=?
  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 pass_td 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(tie=1) AS ties, SUM(pass_cmp) AS pass_cmp, SUM(pass_att) AS pass_att, (SUM(pass_att)-SUM(pass_cmp)) AS pass_inc, IF(SUM(pass_att), SUM(pass_cmp)/SUM(pass_att), NULL) as pass_cmp_pct_csk, ROUND(100 * (IF(SUM(pass_att), SUM(pass_cmp)/SUM(pass_att), NULL)),1) AS pass_cmp_pct, SUM(pass_yds) AS pass_yds, SUM(pass_td) AS pass_td, SUM(pass_int) AS pass_int, IF(SUM(pass_att), SUM(pass_td)/SUM(pass_att), NULL) as pass_td_pct_csk, ROUND(100 * (IF(SUM(pass_att), SUM(pass_td)/SUM(pass_att), NULL)),1) AS pass_td_pct, IF(SUM(pass_att), SUM(pass_int)/SUM(pass_att), NULL) as pass_int_pct_csk, ROUND(100 * (IF(SUM(pass_att), SUM(pass_int)/SUM(pass_att), NULL)),1) AS pass_int_pct, IF(SUM(pass_att), 
  ROUND(100 *
  (1.0 / 6.0) *
  (LEAST(GREATEST(((SUM(pass_cmp) / SUM(pass_att)) - 0.3) / 0.2, 0), 2.375)
  + LEAST(GREATEST((((SUM(pass_yds)+sum(IFNULL(pass_sacked_yds,0))) / SUM(pass_att)) - 3.0) / 4.0, 0.0), 2.375)
  + LEAST(GREATEST( (SUM(pass_td)  / SUM(pass_att)) / 0.05, 0.0), 2.375)
  + LEAST(GREATEST((0.095 - (SUM(pass_int) / SUM(pass_att))) / 0.04, 0.0), 2.375)
  ),
   1), NULL
  )
  as pass_rating_csk, ROUND(IF(SUM(pass_att), 
  ROUND(100 *
  (1.0 / 6.0) *
  (LEAST(GREATEST(((SUM(pass_cmp) / SUM(pass_att)) - 0.3) / 0.2, 0), 2.375)
  + LEAST(GREATEST((((SUM(pass_yds)+sum(IFNULL(pass_sacked_yds,0))) / SUM(pass_att)) - 3.0) / 4.0, 0.0), 2.375)
  + LEAST(GREATEST( (SUM(pass_td)  / SUM(pass_att)) / 0.05, 0.0), 2.375)
  + LEAST(GREATEST((0.095 - (SUM(pass_int) / SUM(pass_att))) / 0.04, 0.0), 2.375)
  ),
   1), NULL
  ),
  1) AS pass_rating, SUM(pass_sacked) AS pass_sacked, SUM(pass_sacked_yds) AS pass_sacked_yds, IF(SUM(pass_sacked IS NOT NULL)>0,SUM(IF(pass_sacked IS NULL,0,pass_sacked))/SUM(IF(pass_sacked IS NULL,0,pass_att+pass_sacked)) , NULL) as pass_sacked_pct_csk, ROUND(100 * (IF(SUM(pass_sacked IS NOT NULL)>0,SUM(IF(pass_sacked IS NULL,0,pass_sacked))/SUM(IF(pass_sacked IS NULL,0,pass_att+pass_sacked)) , NULL)),2) AS pass_sacked_pct, IF(SUM(pass_att), (SUM(pass_yds)/SUM(pass_att)), NULL) as pass_yds_per_att_csk, ROUND(IF(SUM(pass_att), (SUM(pass_yds)/SUM(pass_att)), NULL),1) AS pass_yds_per_att, SUM(pass_yds)/SUM(IFNULL(pass_sacked,0)+pass_att) as pass_net_yds_per_att_csk, ROUND(SUM(pass_yds)/SUM(IFNULL(pass_sacked,0)+pass_att),2) AS pass_net_yds_per_att, IF(SUM(pass_att),(SUM(pass_yds) + 20.0 * SUM(pass_td) - 45.0 * SUM(pass_int))/SUM(pass_att),NULL) as pass_adj_yds_per_att_csk, ROUND(IF(SUM(pass_att),(SUM(pass_yds) + 20.0 * SUM(pass_td) - 45.0 * SUM(pass_int))/SUM(pass_att),NULL),2) AS pass_adj_yds_per_att, IF(SUM(pass_sacked IS NOT NULL)>0,SUM(IF(pass_sacked IS NULL,0,pass_yds+ (20.0 * pass_td) - (45.0 * pass_int)))/SUM(IF(pass_sacked IS NULL,0,pass_sacked+pass_att)) ,NULL) as pass_adj_net_yds_per_att_csk, ROUND(IF(SUM(pass_sacked IS NOT NULL)>0,SUM(IF(pass_sacked IS NULL,0,pass_yds+ (20.0 * pass_td) - (45.0 * pass_int)))/SUM(IF(pass_sacked IS NULL,0,pass_sacked+pass_att)) ,NULL),2) AS pass_adj_net_yds_per_att, IF(SUM(pass_cmp), SUM(pass_yds)/SUM(pass_cmp), NULL) as pass_yds_per_cmp_csk, ROUND(IF(SUM(pass_cmp), SUM(pass_yds)/SUM(pass_cmp), NULL),1) AS pass_yds_per_cmp
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 type="reg"
  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 pass_td DESC, span_start_date_csk DESC, team_id 

SQL PARAMS -- $VAR1 = [
          2025,
          2025,
          5
        ];

Query Time: 0.35 seconds
Table Build Time: 0.05 seconds


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