Leon Draisaitl vs. Brad Marchand: Head-to-Head Stats Comparison

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

header_comp

WITH
  h2h_record AS (
SELECT p1.person_id as p1_person_id, SUM(p1.win  AND p1.type="reg")  as p1_rs_wins, SUM(p1.win  AND p1.type="post") as p1_po_wins, SUM(p1.loss AND p1.type="reg")  as p1_rs_losses, SUM(p1.loss AND p1.type="post") as p1_po_losses, SUM(p1.tie  AND p1.type="reg")  as p1_rs_ties, SUM(p1.tie  AND p1.type="post") as p1_po_ties, p2.person_id as p2_person_id, SUM(p2.win  AND p2.type="reg")  as p2_rs_wins, SUM(p2.win  AND p2.type="post") as p2_po_wins, SUM(p2.loss AND p2.type="reg")  as p2_rs_losses, SUM(p2.loss AND p2.type="post") as p2_po_losses, SUM(p2.tie  AND p2.type="reg")  as p2_rs_ties, SUM(p2.tie  AND p2.type="post") as p2_po_ties
FROM (SELECT person_id, win as win, (loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) as loss, (tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) as tie, game_id, type, team_id
FROM sup_team_games
  JOIN sup_games USING (game_id)
  JOIN sup_player_games USING (game_id, team_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE person_id=?  ) as p1
  JOIN (SELECT person_id, win as win, (loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) as loss, (tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) as tie, game_id, type, team_id FROM sup_team_games
  JOIN sup_games USING (game_id)
  JOIN sup_player_games USING (game_id, team_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE person_id=?  ) as p2 ON p1.game_id=p2.game_id AND p1.team_id!=p2.team_id
  ),
  h2h_record_tot AS (
SELECT person_id, rs_wins, rs_losses, rs_ties, po_wins, po_losses, po_ties
FROM (SELECT p1_person_id as person_id, p1_rs_wins as rs_wins, p1_rs_losses as rs_losses, p1_rs_ties as rs_ties, p1_po_wins as po_wins, p1_po_losses as po_losses, p1_po_ties as po_ties
FROM h2h_record
  UNION SELECT p2_person_id as person_id, p2_rs_wins as rs_wins, p2_rs_losses as rs_losses, p2_rs_ties as rs_ties, p2_po_wins as po_wins, p2_po_losses as po_losses, p2_po_ties as po_ties
FROM h2h_record) as zzz
WHERE 1  
  )
SELECT person_id, rs_wins, rs_losses, rs_ties, po_wins, po_losses, po_ties, SUM(games) AS games, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points, SUM(plus_minus) AS plus_minus, SUM(pen_min) AS pen_min, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(goals_gw) AS goals_gw, SUM(shots) AS shots, SUM(ps) as ps_csk, ROUND(SUM(ps),1) AS ps
FROM sup_player_team_seasons as st
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  LEFT JOIN h2h_record_tot USING (person_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="reg" 
GROUP BY person_id
ORDER BY person_id 

SQL PARAMS -- $VAR1 = [
          'draisle01',
          'marchbr03',
          'marchbr03',
          'draisle01'
        ];
SELECT name_short, name, award_id FROM sup_awards WHERE award_id IN ("hart","ross","norris","selke","smythe","byng","richard","AS") GROUP BY award_id

SQL PARAMS -- $VAR1 = [];

WITH
  champ AS (
SELECT person_id, COUNT(*) as championships
FROM sup_player_team_seasons
  LEFT JOIN sup_team_seasons USING (team_id, year_id, comp_id, phase_id) 
WHERE person_id=? AND phase_id='NHL_PS' AND is_cup_champion=1 
  ),
  awards AS (
SELECT person_id,
  SUM(award_id="AS") as `AS`, SUM(award_id="byng") as `byng`, SUM(award_id="hart") as `hart`, SUM(award_id="norris") as `norris`, SUM(award_id="richard") as `richard`, SUM(award_id="ross") as `ross`, SUM(award_id="selke") as `selke`, SUM(award_id="smythe") as `smythe`
FROM sup_awards_players
WHERE person_id=? AND comp_id='NHL' 
  )
SELECT person_id, is_hof, championships, awards.* FROM sup_players
  LEFT JOIN awards USING (person_id)
  LEFT JOIN champ USING (person_id)
WHERE person_id=?


SQL PARAMS -- $VAR1 = [
          'draisle01',
          'draisle01',
          'draisle01'
        ];

WITH
  champ AS (
SELECT person_id, COUNT(*) as championships
FROM sup_player_team_seasons
  LEFT JOIN sup_team_seasons USING (team_id, year_id, comp_id, phase_id) 
WHERE person_id=? AND phase_id='NHL_PS' AND is_cup_champion=1 
  ),
  awards AS (
SELECT person_id,
  SUM(award_id="AS") as `AS`, SUM(award_id="byng") as `byng`, SUM(award_id="hart") as `hart`, SUM(award_id="norris") as `norris`, SUM(award_id="richard") as `richard`, SUM(award_id="ross") as `ross`, SUM(award_id="selke") as `selke`, SUM(award_id="smythe") as `smythe`
FROM sup_awards_players
WHERE person_id=? AND comp_id='NHL' 
  )
SELECT person_id, is_hof, championships, awards.* FROM sup_players
  LEFT JOIN awards USING (person_id)
  LEFT JOIN champ USING (person_id)
WHERE person_id=?


SQL PARAMS -- $VAR1 = [
          'marchbr03',
          'marchbr03',
          'marchbr03'
        ];

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

h2h_sum

Page Setup Time: 0.00073 seconds

WITH
  pgames AS (
SELECT person_id, game_id, team_id, year_id
FROM sup_team_games
  JOIN sup_player_games USING (game_id, team_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE comp_id=?  
  ),
  pgames_list AS (
SELECT p1.game_id
FROM (SELECT game_id, team_id
FROM pgames
WHERE person_id=?  ) as p1
  JOIN (SELECT game_id, team_id FROM pgames WHERE person_id=?  ) as p2 ON p1.game_id=p2.game_id AND p1.team_id!=p2.team_id
  )
SELECT person_id, COUNT(*) AS games, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points, SUM(plus_minus) AS plus_minus, SUM(pen_min) AS pen_min, SUM(goals - (goals_pp + goals_sh)) AS goals_ev, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(goals_gw) AS goals_gw, SUM(assists_es) AS assists_es, SUM(assists_pp) AS assists_pp, SUM(assists_sh) AS assists_sh, SUM(shots) AS shots, SUM(goals)/SUM(shots) as shot_pct_csk, ROUND(100 * (SUM(goals)/SUM(shots)),1) AS shot_pct, SUM(time_on_ice)/60 as time_on_ice_csk, IF(SUM(st.time_on_ice),CONCAT_WS(':', FLOOR(SUM(st.time_on_ice) / 60), LPAD(IF(ROUND(MOD(SUM(st.time_on_ice), 60)) >= 60, 0, ROUND(MOD(SUM(st.time_on_ice), 60))), 2, '0')),NULL) AS time_on_ice, SUM(time_on_ice) / SUM(year_id>=1998) as time_on_ice_avg_csk, IF(
  MOD((SUM(time_on_ice) / COUNT(*)), 60) = 60,
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60), LPAD(CAST(MOD((SUM(time_on_ice) / SUM(year_id>=1998)), 60) AS SIGNED),2,'0'))
  ) AS time_on_ice_avg
FROM sup_player_games as st
  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 FROM sup_team_games) as tg USING (game_id, team_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_games USING (game_id)
  JOIN pgames_list USING (game_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="reg" 
GROUP BY person_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'NHL',
          'draisle01',
          'marchbr03',
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.05 seconds
WITH
  pgames AS (
SELECT person_id, game_id, team_id, year_id
FROM sup_team_games
  JOIN sup_player_games USING (game_id, team_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE comp_id=?  
  ),
  pgames_list AS (
SELECT p1.game_id
FROM (SELECT game_id, team_id
FROM pgames
WHERE person_id=?  ) as p1
  JOIN (SELECT game_id, team_id FROM pgames WHERE person_id=?  ) as p2 ON p1.game_id=p2.game_id AND p1.team_id!=p2.team_id
  )
SELECT person_id, COUNT(*) AS games, SUM(goals) AS goals, SUM(assists) AS assists, SUM(points) AS points, SUM(plus_minus) AS plus_minus, SUM(pen_min) AS pen_min, SUM(goals - (goals_pp + goals_sh)) AS goals_ev, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(goals_gw) AS goals_gw, SUM(assists_es) AS assists_es, SUM(assists_pp) AS assists_pp, SUM(assists_sh) AS assists_sh, SUM(shots) AS shots, SUM(goals)/SUM(shots) as shot_pct_csk, ROUND(100 * (SUM(goals)/SUM(shots)),1) AS shot_pct, SUM(time_on_ice)/60 as time_on_ice_csk, IF(SUM(st.time_on_ice),CONCAT_WS(':', FLOOR(SUM(st.time_on_ice) / 60), LPAD(IF(ROUND(MOD(SUM(st.time_on_ice), 60)) >= 60, 0, ROUND(MOD(SUM(st.time_on_ice), 60))), 2, '0')),NULL) AS time_on_ice, SUM(time_on_ice) / SUM(year_id>=1998) as time_on_ice_avg_csk, IF(
  MOD((SUM(time_on_ice) / COUNT(*)), 60) = 60,
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(year_id>=1998)) / 60), LPAD(CAST(MOD((SUM(time_on_ice) / SUM(year_id>=1998)), 60) AS SIGNED),2,'0'))
  ) AS time_on_ice_avg
FROM sup_player_games as st
  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 FROM sup_team_games) as tg USING (game_id, team_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_games USING (game_id)
  JOIN pgames_list USING (game_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="post" 
GROUP BY person_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'NHL',
          'draisle01',
          'marchbr03',
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.05 seconds
Table Build Time: 0.03 seconds

Table Build Time: 0.03 seconds


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

h2h_gamelogs

Page Setup Time: 0.00051 seconds

WITH
  pgames AS (
SELECT person_id, game_id, team_id, year_id
FROM sup_team_games
  JOIN sup_player_games USING (game_id, team_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE comp_id=?  
  ),
  pgames_list AS (
SELECT p1.game_id
FROM (SELECT game_id, team_id
FROM pgames
WHERE person_id=?  ) as p1
  JOIN (SELECT game_id, team_id FROM pgames WHERE person_id=?  ) as p2 ON p1.game_id=p2.game_id AND p1.team_id!=p2.team_id
  )
SELECT person_id, ts.name_abbr as team_name_abbr, ts.link as team_link, game_id, date, sup_games.link as game_link, goals, assists, points, plus_minus, pen_min, goals_pp, goals_sh, goals_gw, shots
FROM sup_player_games as st
  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 FROM sup_team_games) as tg USING (game_id, team_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_games USING (game_id)
  JOIN pgames_list USING (game_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="reg" 
ORDER BY date DESC, person_id LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          'NHL',
          'draisle01',
          'marchbr03',
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.03 seconds
WITH
  pgames AS (
SELECT person_id, game_id, team_id, year_id
FROM sup_team_games
  JOIN sup_player_games USING (game_id, team_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE comp_id=?  
  ),
  pgames_list AS (
SELECT p1.game_id
FROM (SELECT game_id, team_id
FROM pgames
WHERE person_id=?  ) as p1
  JOIN (SELECT game_id, team_id FROM pgames WHERE person_id=?  ) as p2 ON p1.game_id=p2.game_id AND p1.team_id!=p2.team_id
  )
SELECT person_id, ts.name_abbr as team_name_abbr, ts.link as team_link, game_id, date, sup_games.link as game_link, goals, assists, points, plus_minus, pen_min, goals_pp, goals_sh, goals_gw, shots
FROM sup_player_games as st
  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 FROM sup_team_games) as tg USING (game_id, team_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_games USING (game_id)
  JOIN pgames_list USING (game_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="post" 
ORDER BY date DESC, person_id LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          'NHL',
          'draisle01',
          'marchbr03',
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.03 seconds
Table Build Time: 0.03 seconds

Table Build Time: 0.03 seconds


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

player_comp

Page Setup Time: 0.00093 seconds

SELECT person_id, IF(MIN(age)=MAX(age), MIN(age), CONCAT(MIN(age), '-', MAX(age))) as age_range, MIN(year_id) as year_min_csk, CONCAT(MIN(year_id)-1,'-',SUBSTRING(MIN(year_id),3,2)) as year_min, MAX(year_id) as year_max_csk, CONCAT(MAX(year_id)-1,'-',SUBSTRING(MAX(year_id),3,2)) as year_max, SUM(games IS NULL) as games_incomplete, SUM(games) AS games, SUM(goals IS NULL) as goals_incomplete, SUM(goals) AS goals, SUM(assists IS NULL) as assists_incomplete, SUM(assists) AS assists, SUM(points IS NULL) as points_incomplete, SUM(points) AS points, SUM(plus_minus IS NULL) as plus_minus_incomplete, SUM(plus_minus) AS plus_minus, SUM(pen_min IS NULL) as pen_min_incomplete, SUM(pen_min) AS pen_min, SUM(goals_sh IS NULL)+SUM(goals_pp IS NULL)+SUM(goals IS NULL) as goals_ev_incomplete, SUM(goals - (goals_pp + goals_sh)) AS goals_ev, SUM(goals_pp IS NULL) as goals_pp_incomplete, SUM(goals_pp) AS goals_pp, SUM(goals_sh IS NULL) as goals_sh_incomplete, SUM(goals_sh) AS goals_sh, SUM(goals_gw IS NULL) as goals_gw_incomplete, SUM(goals_gw) AS goals_gw, SUM(assists_es IS NULL) as assists_es_incomplete, SUM(assists_es) AS assists_es, SUM(assists_pp IS NULL) as assists_pp_incomplete, SUM(assists_pp) AS assists_pp, SUM(assists_sh IS NULL) as assists_sh_incomplete, SUM(assists_sh) AS assists_sh, SUM(shots IS NULL) as shots_incomplete, SUM(shots) AS shots, SUM(goals*(year_id>=1960))/SUM(shots) as shot_pct_csk, SUM(shots IS NULL)+SUM(goals IS NULL)+SUM(year_id IS NULL) as shot_pct_incomplete, ROUND(100 * (SUM(goals*(year_id>=1960))/SUM(shots)),1) AS shot_pct, SUM(time_on_ice)/60 as time_on_ice_csk, SUM(time_on_ice IS NULL) as time_on_ice_incomplete, IF(SUM(st.time_on_ice),IF(
  MOD(SUM(time_on_ice), 60) = 60,
  CONCAT_WS(':', FLOOR(SUM(time_on_ice) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR(SUM(time_on_ice) / 60), LPAD(MOD(SUM(time_on_ice), 60),2,'0'))
  ),NULL) AS time_on_ice, SUM(time_on_ice) / SUM(games * (year_id>=1998)) as time_on_ice_avg_csk, SUM(time_on_ice IS NULL)+SUM(games IS NULL)+SUM(year_id IS NULL) as time_on_ice_avg_incomplete, IF(
  MOD((SUM(time_on_ice) / SUM(games * (year_id>=1998))), 60) = 60,
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(games * (year_id>=1998))) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(games * (year_id>=1998))) / 60), LPAD(CAST(MOD((SUM(time_on_ice) / SUM(games * (year_id>=1998))), 60) AS SIGNED),2,'0'))
  ) AS time_on_ice_avg
FROM sup_player_team_seasons as st
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="reg" 
GROUP BY person_id
ORDER BY person_id 

SQL PARAMS -- $VAR1 = [
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.00 seconds
SELECT person_id, IF(MIN(age)=MAX(age), MIN(age), CONCAT(MIN(age), '-', MAX(age))) as age_range, MIN(year_id) as year_min_csk, CONCAT(MIN(year_id)-1,'-',SUBSTRING(MIN(year_id),3,2)) as year_min, MAX(year_id) as year_max_csk, CONCAT(MAX(year_id)-1,'-',SUBSTRING(MAX(year_id),3,2)) as year_max, SUM(games IS NULL) as games_incomplete, SUM(games) AS games, SUM(goals IS NULL) as goals_incomplete, SUM(goals) AS goals, SUM(assists IS NULL) as assists_incomplete, SUM(assists) AS assists, SUM(points IS NULL) as points_incomplete, SUM(points) AS points, SUM(plus_minus IS NULL) as plus_minus_incomplete, SUM(plus_minus) AS plus_minus, SUM(pen_min IS NULL) as pen_min_incomplete, SUM(pen_min) AS pen_min, SUM(goals_sh IS NULL)+SUM(goals_pp IS NULL)+SUM(goals IS NULL) as goals_ev_incomplete, SUM(goals - (goals_pp + goals_sh)) AS goals_ev, SUM(goals_pp IS NULL) as goals_pp_incomplete, SUM(goals_pp) AS goals_pp, SUM(goals_sh IS NULL) as goals_sh_incomplete, SUM(goals_sh) AS goals_sh, SUM(goals_gw IS NULL) as goals_gw_incomplete, SUM(goals_gw) AS goals_gw, SUM(assists_es IS NULL) as assists_es_incomplete, SUM(assists_es) AS assists_es, SUM(assists_pp IS NULL) as assists_pp_incomplete, SUM(assists_pp) AS assists_pp, SUM(assists_sh IS NULL) as assists_sh_incomplete, SUM(assists_sh) AS assists_sh, SUM(shots IS NULL) as shots_incomplete, SUM(shots) AS shots, SUM(goals*(year_id>=1960))/SUM(shots) as shot_pct_csk, SUM(shots IS NULL)+SUM(goals IS NULL)+SUM(year_id IS NULL) as shot_pct_incomplete, ROUND(100 * (SUM(goals*(year_id>=1960))/SUM(shots)),1) AS shot_pct, SUM(time_on_ice)/60 as time_on_ice_csk, SUM(time_on_ice IS NULL) as time_on_ice_incomplete, IF(SUM(st.time_on_ice),IF(
  MOD(SUM(time_on_ice), 60) = 60,
  CONCAT_WS(':', FLOOR(SUM(time_on_ice) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR(SUM(time_on_ice) / 60), LPAD(MOD(SUM(time_on_ice), 60),2,'0'))
  ),NULL) AS time_on_ice, SUM(time_on_ice) / SUM(games * (year_id>=1998)) as time_on_ice_avg_csk, SUM(time_on_ice IS NULL)+SUM(games IS NULL)+SUM(year_id IS NULL) as time_on_ice_avg_incomplete, IF(
  MOD((SUM(time_on_ice) / SUM(games * (year_id>=1998))), 60) = 60,
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(games * (year_id>=1998))) / 60) + 1, '00'),
  CONCAT_WS(':', FLOOR((SUM(time_on_ice) / SUM(games * (year_id>=1998))) / 60), LPAD(CAST(MOD((SUM(time_on_ice) / SUM(games * (year_id>=1998))), 60) AS SIGNED),2,'0'))
  ) AS time_on_ice_avg
FROM sup_player_team_seasons as st
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="post" 
GROUP BY person_id
ORDER BY person_id 

SQL PARAMS -- $VAR1 = [
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.00 seconds
Table Build Time: 0.03 seconds

Table Build Time: 0.03 seconds


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

player_misc

Page Setup Time: 0.00095 seconds

SELECT person_id, IF(MIN(age)=MAX(age), MIN(age), CONCAT(MIN(age), '-', MAX(age))) as age_range, MIN(year_id) as year_min_csk, CONCAT(MIN(year_id)-1,'-',SUBSTRING(MIN(year_id),3,2)) as year_min, MAX(year_id) as year_max_csk, CONCAT(MAX(year_id)-1,'-',SUBSTRING(MAX(year_id),3,2)) as year_max, SUM(goals_created) as goals_created_csk, SUM(goals_created IS NULL) as goals_created_incomplete, ROUND(SUM(goals_created),1) AS goals_created, SUM(goals)/SUM(games) as goals_per_game_csk, SUM(games IS NULL)+SUM(goals IS NULL) as goals_per_game_incomplete, ROUND(SUM(goals)/SUM(games),2) AS goals_per_game, SUM(assists)/SUM(games) as assists_per_game_csk, SUM(games IS NULL)+SUM(assists IS NULL) as assists_per_game_incomplete, ROUND(SUM(assists)/SUM(games),2) AS assists_per_game, SUM(points)/SUM(games) as points_per_game_csk, SUM(points IS NULL)+SUM(games IS NULL) as points_per_game_incomplete, ROUND(SUM(points)/SUM(games),2) AS points_per_game, (SUM(shots)/SUM(games)) as shots_per_game_csk, SUM(shots IS NULL)+SUM(games IS NULL) as shots_per_game_incomplete, ROUND((SUM(shots)/SUM(games)),2) AS shots_per_game, SUM(goals_created)/SUM(games) as goals_created_per_game_csk, SUM(games IS NULL)+SUM(goals_created IS NULL) as goals_created_per_game_incomplete, ROUND(SUM(goals_created)/SUM(games),2) AS goals_created_per_game, SUM(goals_adjusted IS NULL) as goals_adjusted_incomplete, SUM(goals_adjusted) AS goals_adjusted, SUM(assists_adjusted IS NULL) as assists_adjusted_incomplete, SUM(assists_adjusted) AS assists_adjusted, SUM(points_adjusted IS NULL) as points_adjusted_incomplete, SUM(points_adjusted) AS points_adjusted, SUM(goals_created_adjusted) as goals_created_adjusted_csk, SUM(goals_created_adjusted IS NULL) as goals_created_adjusted_incomplete, ROUND(SUM(goals_created_adjusted),1) AS goals_created_adjusted, SUM(total_goals_for IS NULL) as total_goals_for_incomplete, SUM(total_goals_for) AS total_goals_for, SUM(power_play_goals_for IS NULL) as power_play_goals_for_incomplete, SUM(power_play_goals_for) AS power_play_goals_for, SUM(total_goals_against IS NULL) as total_goals_against_incomplete, SUM(total_goals_against) AS total_goals_against, SUM(power_play_goals_against IS NULL) as power_play_goals_against_incomplete, SUM(power_play_goals_against) AS power_play_goals_against, SUM(plus_minus IS NULL) as plus_minus_incomplete, SUM(plus_minus) AS plus_minus, SUM(ps) as ps_csk, SUM(ps IS NULL) as ps_incomplete, ROUND(SUM(ps),1) AS ps, SUM(ops) as ops_csk, SUM(ops IS NULL) as ops_incomplete, ROUND(SUM(ops),1) AS ops, SUM(dps) as dps_csk, SUM(dps IS NULL) as dps_incomplete, ROUND(SUM(dps),1) AS dps
FROM sup_player_team_seasons as st
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="reg" 
GROUP BY person_id
ORDER BY person_id 

SQL PARAMS -- $VAR1 = [
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.00 seconds
SELECT person_id, IF(MIN(age)=MAX(age), MIN(age), CONCAT(MIN(age), '-', MAX(age))) as age_range, MIN(year_id) as year_min_csk, CONCAT(MIN(year_id)-1,'-',SUBSTRING(MIN(year_id),3,2)) as year_min, MAX(year_id) as year_max_csk, CONCAT(MAX(year_id)-1,'-',SUBSTRING(MAX(year_id),3,2)) as year_max, SUM(goals_created) as goals_created_csk, SUM(goals_created IS NULL) as goals_created_incomplete, ROUND(SUM(goals_created),1) AS goals_created, SUM(goals)/SUM(games) as goals_per_game_csk, SUM(games IS NULL)+SUM(goals IS NULL) as goals_per_game_incomplete, ROUND(SUM(goals)/SUM(games),2) AS goals_per_game, SUM(assists)/SUM(games) as assists_per_game_csk, SUM(games IS NULL)+SUM(assists IS NULL) as assists_per_game_incomplete, ROUND(SUM(assists)/SUM(games),2) AS assists_per_game, SUM(points)/SUM(games) as points_per_game_csk, SUM(points IS NULL)+SUM(games IS NULL) as points_per_game_incomplete, ROUND(SUM(points)/SUM(games),2) AS points_per_game, (SUM(shots)/SUM(games)) as shots_per_game_csk, SUM(shots IS NULL)+SUM(games IS NULL) as shots_per_game_incomplete, ROUND((SUM(shots)/SUM(games)),2) AS shots_per_game, SUM(goals_created)/SUM(games) as goals_created_per_game_csk, SUM(games IS NULL)+SUM(goals_created IS NULL) as goals_created_per_game_incomplete, ROUND(SUM(goals_created)/SUM(games),2) AS goals_created_per_game, SUM(goals_adjusted IS NULL) as goals_adjusted_incomplete, SUM(goals_adjusted) AS goals_adjusted, SUM(assists_adjusted IS NULL) as assists_adjusted_incomplete, SUM(assists_adjusted) AS assists_adjusted, SUM(points_adjusted IS NULL) as points_adjusted_incomplete, SUM(points_adjusted) AS points_adjusted, SUM(goals_created_adjusted) as goals_created_adjusted_csk, SUM(goals_created_adjusted IS NULL) as goals_created_adjusted_incomplete, ROUND(SUM(goals_created_adjusted),1) AS goals_created_adjusted, SUM(total_goals_for IS NULL) as total_goals_for_incomplete, SUM(total_goals_for) AS total_goals_for, SUM(power_play_goals_for IS NULL) as power_play_goals_for_incomplete, SUM(power_play_goals_for) AS power_play_goals_for, SUM(total_goals_against IS NULL) as total_goals_against_incomplete, SUM(total_goals_against) AS total_goals_against, SUM(power_play_goals_against IS NULL) as power_play_goals_against_incomplete, SUM(power_play_goals_against) AS power_play_goals_against, SUM(plus_minus IS NULL) as plus_minus_incomplete, SUM(plus_minus) AS plus_minus
FROM sup_player_team_seasons as st
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
WHERE ((person_id=?) OR (person_id=?))
  AND type="post" 
GROUP BY person_id
ORDER BY person_id 

SQL PARAMS -- $VAR1 = [
          'marchbr03',
          'draisle01'
        ];

Query Time: 0.00 seconds
Table Build Time: 0.03 seconds

Table Build Time: 0.03 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          't1yrto' => 2025,
          'player_id1' => 'draisle01',
          't2yrto' => 2025,
          'comp_id' => 'NHL',
          'player_id2' => 'marchbr03',
          'match' => 'versus_playervplayer'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'comp_id' => 'NHL',
          'player_id1' => 'draisle01',
          'player_id2' => 'marchbr03'
        };