Nashville Predators vs. Arizona Coyotes: Head-to-Head Reg Season and Playoffs Stats Comparison

Unknown column 'st.game_id' in 'where clause'

SELECT IF(away_team_id="NSH","@","") as loc, IF(away_team_id="NSH",away_score,home_score) as team1_score, IF(away_team_id="PHX",away_score,home_score) as team2_score, game_id, date, game_id, sup_games.link as game_link, sup_venue_seasons.name as venue_name, city as venue_city, overtimes as overtimes_csk, (SELECT IF(overtimes>0, IF(is_shootout,'SO','OT'),NULL) FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id) as overtimes
FROM sup_games
  JOIN (SELECT game_id, year_id, comp_id, phase_id, team_id as away_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, away_team_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  LEFT JOIN sup_venues USING (venue_id)
  LEFT JOIN sup_venue_seasons USING (venue_id, year_id)
WHERE ((away_team_id=? AND home_team_id=?) OR (away_team_id=? AND home_team_id=?))
  AND home_score IS NOT NULL
  AND type="reg" 
ORDER BY date DESC LIMIT 0, 20
--------------------------------------

h2h_records

Page Setup Time: 0.00090 seconds

WITH
  tr1 AS (
SELECT year_id, comp_id, COUNT(*) AS games, SUM(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) AS ties_plus_lot, SUM(goals) AS goals, SUM(goals)/COUNT(*) as goals_per_game_csk, ROUND(SUM(goals)/COUNT(*),2) AS goals_per_game
FROM sup_team_games
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND opp_team_id=?
  AND type="reg" 
GROUP BY year_id 
  ),
  tr2 AS (
SELECT year_id, comp_id, COUNT(*) AS games, SUM(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) AS ties_plus_lot, SUM(goals) AS goals, SUM(goals)/COUNT(*) as goals_per_game_csk, ROUND(SUM(goals)/COUNT(*),2) AS goals_per_game
FROM sup_team_games
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND opp_team_id=?
  AND type="reg" 
GROUP BY year_id 
  )
SELECT year_id as year_id_csk, CONCAT(year_id-1,"-",SUBSTRING(year_id,3,2)) as year_id, tr1.comp_id, tr1.games AS tr1_games, tr2.games AS tr2_games, tr1.wins AS tr1_wins, tr2.wins AS tr2_wins, tr1.losses AS tr1_losses, tr2.losses AS tr2_losses, tr1.ties_plus_lot AS tr1_ties_plus_lot, tr2.ties_plus_lot AS tr2_ties_plus_lot, tr1.goals AS tr1_goals, tr2.goals AS tr2_goals, tr1.goals_per_game AS tr1_goals_per_game, tr2.goals_per_game AS tr2_goals_per_game
FROM tr1
  JOIN tr2 USING (year_id)
WHERE 1 
GROUP BY year_id
ORDER BY year_id 

SQL PARAMS -- $VAR1 = [
          'NSH',
          'PHX',
          'PHX',
          'NSH'
        ];

Query Time: 0.00 seconds
WITH
  tr1 AS (
SELECT year_id, comp_id, COUNT(*) AS games, SUM(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) AS ties_plus_lot, SUM(goals) AS goals, SUM(goals)/COUNT(*) as goals_per_game_csk, ROUND(SUM(goals)/COUNT(*),2) AS goals_per_game
FROM sup_team_games
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND opp_team_id=?
  AND type="post" 
GROUP BY year_id 
  ),
  tr2 AS (
SELECT year_id, comp_id, COUNT(*) AS games, SUM(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) AS ties_plus_lot, SUM(goals) AS goals, SUM(goals)/COUNT(*) as goals_per_game_csk, ROUND(SUM(goals)/COUNT(*),2) AS goals_per_game
FROM sup_team_games
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND opp_team_id=?
  AND type="post" 
GROUP BY year_id 
  )
SELECT year_id as year_id_csk, CONCAT(year_id-1,"-",SUBSTRING(year_id,3,2)) as year_id, tr1.comp_id, tr1.games AS tr1_games, tr2.games AS tr2_games, tr1.wins AS tr1_wins, tr2.wins AS tr2_wins, tr1.losses AS tr1_losses, tr2.losses AS tr2_losses, tr1.ties_plus_lot AS tr1_ties_plus_lot, tr2.ties_plus_lot AS tr2_ties_plus_lot, tr1.goals AS tr1_goals, tr2.goals AS tr2_goals, tr1.goals_per_game AS tr1_goals_per_game, tr2.goals_per_game AS tr2_goals_per_game
FROM tr1
  JOIN tr2 USING (year_id)
WHERE 1 
GROUP BY year_id
ORDER BY year_id 

SQL PARAMS -- $VAR1 = [
          'NSH',
          'PHX',
          'PHX',
          'NSH'
        ];

Query Time: 0.00 seconds

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

h2h_sum

Page Setup Time: 0.00087 seconds

WITH
  t1gms AS (
SELECT game_id
FROM sup_team_games
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND type="reg"  
  ),
  t2gms AS (
SELECT game_id
FROM sup_team_games
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND type="reg"  
  )
SELECT (SELECT name_abbr FROM sup_team_seasons WHERE team_id=st.team_id AND sup_team_seasons.year_id=MAX(st.year_id) LIMIT 1) as franch_abbr, team_id, 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(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) AS ties_plus_lot, COUNT(*) AS games, SUM(goals) AS goals, SUM(goals)/COUNT(*) as goals_per_game_csk, ROUND(SUM(goals)/COUNT(*),2) AS goals_per_game, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(shots) AS shots, SUM(goals)/SUM(shots) as shot_pct_csk, ROUND(100 * (SUM(goals)/SUM(shots)),1) AS shot_pct, SUM(goals_against=0) AS shutouts
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
  JOIN t1gms USING (game_id)
  JOIN t2gms USING (game_id)
WHERE home_score IS NOT NULL 
GROUP BY team_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'NSH',
          'PHX'
        ];

Query Time: 0.00 seconds
WITH
  t1gms AS (
SELECT game_id
FROM sup_team_games
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND type="post"  
  ),
  t2gms AS (
SELECT game_id
FROM sup_team_games
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE (team_id=?)
  AND type="post"  
  )
SELECT (SELECT name_abbr FROM sup_team_seasons WHERE team_id=st.team_id AND sup_team_seasons.year_id=MAX(st.year_id) LIMIT 1) as franch_abbr, team_id, 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(win=1) AS wins, SUM(loss=1 AND (year_id<2000 OR type="post" OR overtimes=0)) AS losses, SUM(tie=1 OR (year_id>=2000 AND type="reg" AND loss=1 AND overtimes=1)) AS ties_plus_lot, COUNT(*) AS games, SUM(goals) AS goals, SUM(goals)/COUNT(*) as goals_per_game_csk, ROUND(SUM(goals)/COUNT(*),2) AS goals_per_game, SUM(goals_pp) AS goals_pp, SUM(goals_sh) AS goals_sh, SUM(shots) AS shots, SUM(goals)/SUM(shots) as shot_pct_csk, ROUND(100 * (SUM(goals)/SUM(shots)),1) AS shot_pct, SUM(goals_against=0) AS shutouts
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
  JOIN t1gms USING (game_id)
  JOIN t2gms USING (game_id)
WHERE home_score IS NOT NULL 
GROUP BY team_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'NSH',
          'PHX'
        ];

Query Time: 0.00 seconds

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

overall_sum

Page Setup Time: 0.00111 seconds

SELECT (SELECT name_abbr FROM sup_team_seasons WHERE team_id=st.team_id AND sup_team_seasons.year_id=MAX(st.year_id) LIMIT 1) as franch_abbr, team_id, 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(wins IS NULL) as wins_incomplete, SUM(wins) AS wins, SUM(losses IS NULL) as losses_incomplete, SUM(losses) AS losses, SUM(ties IS NULL)+SUM(losses_ot IS NULL) as ties_plus_lot_incomplete, SUM(IFNULL(ties,0))+SUM(IFNULL(losses_ot,0)) AS ties_plus_lot, SUM(games IS NULL) as games_incomplete, SUM(games) AS games, SUM(goals)/SUM(games) as goals_per_game_csk, SUM(goals IS NULL)+SUM(games IS NULL) as goals_per_game_incomplete, ROUND(SUM(goals)/SUM(games),2) AS goals_per_game, SUM(opp_goals IS NULL) as opp_goals_incomplete, SUM(opp_goals) AS opp_goals, SUM(opp_goals)/SUM(games) as goals_against_per_game_csk, SUM(opp_goals IS NULL)+SUM(games IS NULL) as goals_against_per_game_incomplete, ROUND(SUM(opp_goals)/SUM(games),2) AS goals_against_per_game, SUM(goals_pp IS NULL) as goals_pp_incomplete, SUM(goals_pp) AS goals_pp, IF(SUM(chances_pp), SUM(goals_pp*(year_id>=1964)) / SUM(chances_pp*(year_id>=1964)), NULL) as power_play_pct_csk, SUM(goals_pp IS NULL)+SUM(chances_pp IS NULL)+SUM(year_id IS NULL) as power_play_pct_incomplete, ROUND(100 * (IF(SUM(chances_pp), SUM(goals_pp*(year_id>=1964)) / SUM(chances_pp*(year_id>=1964)), NULL)),1) AS power_play_pct, SUM(goals_sh IS NULL) as goals_sh_incomplete, SUM(goals_sh) AS goals_sh, IF(SUM(opp_chances_pp), (SUM(opp_chances_pp*(year_id>=1964)) - SUM(opp_goals_pp*(year_id>=1964))) / SUM(opp_chances_pp), NULL) as pen_kill_pct_csk, SUM(year_id IS NULL)+SUM(opp_chances_pp IS NULL)+SUM(opp_goals_pp IS NULL) as pen_kill_pct_incomplete, ROUND(100 * (IF(SUM(opp_chances_pp), (SUM(opp_chances_pp*(year_id>=1964)) - SUM(opp_goals_pp*(year_id>=1964))) / SUM(opp_chances_pp), NULL)),1) AS pen_kill_pct, SUM(opp_goals_pp IS NULL) as opp_goals_pp_incomplete, SUM(opp_goals_pp) AS opp_goals_pp, SUM(opp_goals_sh IS NULL) as opp_goals_sh_incomplete, SUM(opp_goals_sh) AS opp_goals_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(year_id IS NULL)+SUM(goals IS NULL) as shot_pct_incomplete, ROUND(100 * (SUM(goals*(year_id>=1960))/SUM(shots)),1) AS shot_pct, SUM(opp_shots IS NULL) as opp_shots_incomplete, SUM(opp_shots) AS opp_shots, SUM(opp_shots - opp_goals) / SUM(opp_shots) as save_pct_team_csk, SUM(opp_shots IS NULL)+SUM(opp_goals IS NULL) as save_pct_team_incomplete, TRIM(LEADING '0' FROM ROUND(SUM(opp_shots - opp_goals) / SUM(opp_shots),3)) AS save_pct_team
FROM sup_team_seasons as st
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE ((team_id=?) OR (team_id=?))
  AND comp_id=?
  AND type="reg" 
GROUP BY team_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'NSH',
          'PHX',
          'NHL'
        ];

Query Time: 0.00 seconds
SELECT (SELECT name_abbr FROM sup_team_seasons WHERE team_id=st.team_id AND sup_team_seasons.year_id=MAX(st.year_id) LIMIT 1) as franch_abbr, team_id, 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(wins IS NULL) as wins_incomplete, SUM(wins) AS wins, SUM(losses IS NULL) as losses_incomplete, SUM(losses) AS losses, SUM(ties IS NULL)+SUM(losses_ot IS NULL) as ties_plus_lot_incomplete, SUM(IFNULL(ties,0))+SUM(IFNULL(losses_ot,0)) AS ties_plus_lot, SUM(games IS NULL) as games_incomplete, SUM(games) AS games, SUM(goals)/SUM(games) as goals_per_game_csk, SUM(goals IS NULL)+SUM(games IS NULL) as goals_per_game_incomplete, ROUND(SUM(goals)/SUM(games),2) AS goals_per_game, SUM(opp_goals IS NULL) as opp_goals_incomplete, SUM(opp_goals) AS opp_goals, SUM(opp_goals)/SUM(games) as goals_against_per_game_csk, SUM(opp_goals IS NULL)+SUM(games IS NULL) as goals_against_per_game_incomplete, ROUND(SUM(opp_goals)/SUM(games),2) AS goals_against_per_game, SUM(goals_pp IS NULL) as goals_pp_incomplete, SUM(goals_pp) AS goals_pp, IF(SUM(chances_pp), SUM(goals_pp*(year_id>=1964)) / SUM(chances_pp*(year_id>=1964)), NULL) as power_play_pct_csk, SUM(goals_pp IS NULL)+SUM(chances_pp IS NULL)+SUM(year_id IS NULL) as power_play_pct_incomplete, ROUND(100 * (IF(SUM(chances_pp), SUM(goals_pp*(year_id>=1964)) / SUM(chances_pp*(year_id>=1964)), NULL)),1) AS power_play_pct, SUM(goals_sh IS NULL) as goals_sh_incomplete, SUM(goals_sh) AS goals_sh, IF(SUM(opp_chances_pp), (SUM(opp_chances_pp*(year_id>=1964)) - SUM(opp_goals_pp*(year_id>=1964))) / SUM(opp_chances_pp), NULL) as pen_kill_pct_csk, SUM(year_id IS NULL)+SUM(opp_chances_pp IS NULL)+SUM(opp_goals_pp IS NULL) as pen_kill_pct_incomplete, ROUND(100 * (IF(SUM(opp_chances_pp), (SUM(opp_chances_pp*(year_id>=1964)) - SUM(opp_goals_pp*(year_id>=1964))) / SUM(opp_chances_pp), NULL)),1) AS pen_kill_pct, SUM(opp_goals_pp IS NULL) as opp_goals_pp_incomplete, SUM(opp_goals_pp) AS opp_goals_pp, SUM(opp_goals_sh IS NULL) as opp_goals_sh_incomplete, SUM(opp_goals_sh) AS opp_goals_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(year_id IS NULL)+SUM(goals IS NULL) as shot_pct_incomplete, ROUND(100 * (SUM(goals*(year_id>=1960))/SUM(shots)),1) AS shot_pct, SUM(opp_shots IS NULL) as opp_shots_incomplete, SUM(opp_shots) AS opp_shots, SUM(opp_shots - opp_goals) / SUM(opp_shots) as save_pct_team_csk, SUM(opp_shots IS NULL)+SUM(opp_goals IS NULL) as save_pct_team_incomplete, TRIM(LEADING '0' FROM ROUND(SUM(opp_shots - opp_goals) / SUM(opp_shots),3)) AS save_pct_team
FROM sup_team_seasons as st
  JOIN sup_phase_seasons USING (year_id, comp_id, phase_id)
WHERE ((team_id=?) OR (team_id=?))
  AND comp_id=?
  AND type="post" 
GROUP BY team_id LIMIT 0, 200

SQL PARAMS -- $VAR1 = [
          'NSH',
          'PHX',
          'NHL'
        ];

Query Time: 0.00 seconds

--------------------------------------
SH->PARAM -- $VAR1 = {
          'comp_id' => 'NHL',
          'team_id2' => 'PHX',
          'team_id1' => 'NSH',
          'match' => 'versus_teamvteam'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'comp_id' => 'NHL',
          'team_id2' => 'PHX',
          'team_id1' => 'NSH',
          'match' => 'versus_teamvteam'
        };