Player Span Stats Finder - Pro Football

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

main

Page Setup Time: 0.00175 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(fantasy_points) OVER w AS fantasy_points
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)
  JOIN sup_comp_seasons USING (comp_id, year_id)
WHERE year_id>=?
  AND type="reg"
  AND comp_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 fantasy_points 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, SUM(fantasy_points) as fantasy_points_csk, SUM(fantasy_points IS NULL) as fantasy_points_incomplete, ROUND(SUM(fantasy_points),2) AS fantasy_points, SUM(IFNULL(rec,0)) + SUM(fantasy_points) as fantasy_points_ppr_csk, SUM(fantasy_points IS NULL)+SUM(rec IS NULL) as fantasy_points_ppr_incomplete, ROUND(SUM(IFNULL(rec,0)) + SUM(fantasy_points),2) AS fantasy_points_ppr, IFNULL(4*SUM(pass_td) + SUM(pass_yds)/25 + SUM(IF(pass_yds>=300,3,0)) - SUM(pass_int) + SUM(rush_yds)/10 + 6*SUM(rush_td) + SUM(IF(rush_yds>=100,3,0)) + SUM(rec_yds)/10 + SUM(rec) + 6*SUM(rec_td) + SUM(IF(rec_yds>=100,3,0)) + 6*SUM(punt_ret_td) + 6*SUM(kick_ret_td) - SUM(IF(year_id>=1994,IFNULL(fumbles_lost,0),fumbles/2)) + 6*SUM(fumbles_rec_td) + 2 * SUM(IFNULL(two_pt_md,0)) + 2*SUM(IFNULL(two_pt_pass,0)) + 3*SUM(IFNULL(fgm1,0)+IFNULL(fgm2,0)+IFNULL(fgm3,0)) + 4*SUM(IFNULL(fgm4,0)) + 5*SUM(IFNULL(fgm5,0)) + SUM(xpm),0) as draftkings_points_csk, SUM(rush_yds IS NULL)+SUM(rush_td IS NULL)+SUM(fgm3 IS NULL)+SUM(two_pt_md IS NULL)+SUM(rec_td IS NULL)+SUM(fgm1 IS NULL)+SUM(punt_ret_td IS NULL)+SUM(year_id IS NULL)+SUM(two_pt_pass IS NULL)+SUM(fgm2 IS NULL)+SUM(fgm4 IS NULL)+SUM(pass_td IS NULL)+SUM(pass_yds IS NULL)+SUM(rec IS NULL)+SUM(fumbles_lost IS NULL)+SUM(rec_yds IS NULL)+SUM(pass_int IS NULL)+SUM(fumbles_rec_td IS NULL)+SUM(kick_ret_td IS NULL)+SUM(xpm IS NULL)+SUM(fgm5 IS NULL)+SUM(3 IS NULL)+SUM(fumbles IS NULL) as draftkings_points_incomplete, ROUND(IFNULL(4*SUM(pass_td) + SUM(pass_yds)/25 + SUM(IF(pass_yds>=300,3,0)) - SUM(pass_int) + SUM(rush_yds)/10 + 6*SUM(rush_td) + SUM(IF(rush_yds>=100,3,0)) + SUM(rec_yds)/10 + SUM(rec) + 6*SUM(rec_td) + SUM(IF(rec_yds>=100,3,0)) + 6*SUM(punt_ret_td) + 6*SUM(kick_ret_td) - SUM(IF(year_id>=1994,IFNULL(fumbles_lost,0),fumbles/2)) + 6*SUM(fumbles_rec_td) + 2 * SUM(IFNULL(two_pt_md,0)) + 2*SUM(IFNULL(two_pt_pass,0)) + 3*SUM(IFNULL(fgm1,0)+IFNULL(fgm2,0)+IFNULL(fgm3,0)) + 4*SUM(IFNULL(fgm4,0)) + 5*SUM(IFNULL(fgm5,0)) + SUM(xpm),0),1) AS draftkings_points, IFNULL(SUM(rush_yds)/10 + 6*SUM(rush_td) + SUM(pass_yds)/25 + 4*SUM(pass_td) - SUM(pass_int) + SUM(rec_yds)/10 + 6*SUM(rec_td) + SUM(rec)/2 + 6*SUM(kick_ret_td) + 6*SUM(punt_ret_td) - 2*SUM(IF(year_id>=1994,IFNULL(fumbles_lost,0),fumbles/2)) + 6*SUM(fumbles_rec_td) + 2*SUM(IFNULL(two_pt_md,0)) + 2*SUM(IFNULL(two_pt_pass,0)) + 3*SUM(IFNULL(fgm1,0)+IFNULL(fgm2,0)+IFNULL(fgm3,0)) + 4*SUM(IFNULL(fgm4,0)) + 5*SUM(IFNULL(fgm5,0)) + SUM(xpm),0) as fanduel_points_csk, SUM(two_pt_pass IS NULL)+SUM(year_id IS NULL)+SUM(fgm2 IS NULL)+SUM(punt_ret_td IS NULL)+SUM(rec IS NULL)+SUM(pass_yds IS NULL)+SUM(fgm4 IS NULL)+SUM(pass_td IS NULL)+SUM(two_pt_md IS NULL)+SUM(fgm3 IS NULL)+SUM(rush_td IS NULL)+SUM(rush_yds IS NULL)+SUM(rec_td IS NULL)+SUM(fgm1 IS NULL)+SUM(kick_ret_td IS NULL)+SUM(fumbles IS NULL)+SUM(fgm5 IS NULL)+SUM(xpm IS NULL)+SUM(rec_yds IS NULL)+SUM(fumbles_lost IS NULL)+SUM(fumbles_rec_td IS NULL)+SUM(pass_int IS NULL) as fanduel_points_incomplete, ROUND(IFNULL(SUM(rush_yds)/10 + 6*SUM(rush_td) + SUM(pass_yds)/25 + 4*SUM(pass_td) - SUM(pass_int) + SUM(rec_yds)/10 + 6*SUM(rec_td) + SUM(rec)/2 + 6*SUM(kick_ret_td) + 6*SUM(punt_ret_td) - 2*SUM(IF(year_id>=1994,IFNULL(fumbles_lost,0),fumbles/2)) + 6*SUM(fumbles_rec_td) + 2*SUM(IFNULL(two_pt_md,0)) + 2*SUM(IFNULL(two_pt_pass,0)) + 3*SUM(IFNULL(fgm1,0)+IFNULL(fgm2,0)+IFNULL(fgm3,0)) + 4*SUM(IFNULL(fgm4,0)) + 5*SUM(IFNULL(fgm5,0)) + SUM(xpm),0),1) AS fanduel_points
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 type="reg"
  AND comp_id=?
  AND date>=span_start_date
  AND date<=span_end_date 
GROUP BY person_id, span_start_date, span_end_date
ORDER BY fantasy_points_csk DESC, span_start_date_csk DESC, person_id 

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

Query Time: 0.33 seconds
Table Build Time: 0.04 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'weight_max' => '500',
          'year_max' => 2025,
          'span_length' => 5,
          'rookie' => 'N',
          'year_min' => 2025,
          'season_end' => -1,
          'season_start' => 1,
          'order_by' => 'fantasy_points',
          'comp_type' => 'reg',
          'match' => 'player_span_game',
          'comp_id' => 'NFL',
          'timeframe' => 'seasons'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_min' => 2025,
          'year_max' => 2025,
          'comp_id' => 'NFL'
        };