Goal Finder

/* mysql8 */
SELECT /* goal_finder:1 */ CASE WHEN is_shootout = 1 THEN 'SO' WHEN overtimes = 1 THEN 'OT' WHEN overtimes > 1 THEN CONCAT(overtimes, 'OT') ELSE '' END AS overtimes,
  period,
  IF(goalie_id IS NOT NULL, CONCAT('/players/', LEFT(goalie_id, 1), '/', goalie_id, '.html'), NULL) AS goalie_link,
  CONCAT_WS(',', m.name_last, m.name_first) AS scorer_csk,
  CONCAT(m.name_first,' ',m.name_last) AS scorer,
  TIME_TO_SEC(time_elapsed) + ((period-1) * 3600) AS total_time,
  overtimes AS overtimes_csk,
  st.goals - st.opp_goals AS game_result_csk,
  IF(st.game_location = 'A', '@', IF(st.game_location = 'N', 'vs.', '')) AS game_location,
  IF(assist2_id IS NOT NULL, CONCAT('/players/', LEFT(assist2_id, 1), '/', assist2_id, '.html'), NULL) AS assist2_link,
  CONCAT('/boxscores/', st.game_id, '.html') AS date_game_link,
  IF(assist2_id IS NOT NULL, CONCAT(a2.name_first,' ',a2.name_last), NULL) AS assist2,
  CONCAT_WS(
  '-', 
  TIMESTAMPDIFF(YEAR, CONCAT_WS('-', m.birth_year, m.birth_month, m.birth_day), st.date_game),
  LPAD(
  IF(
  IFNULL (
  TIMESTAMPDIFF(DAY, CONCAT_WS('-', YEAR(st.date_game), m.birth_month, m.birth_day), st.date_game),
  TIMESTAMPDIFF(DAY, CONCAT_WS('-', YEAR(st.date_game), '03',           '01'      ), st.date_game)
  ) < 0,
  IFNULL (
  TIMESTAMPDIFF(DAY, CONCAT_WS('-', YEAR(st.date_game) - 1, m.birth_month, m.birth_day), st.date_game),
  TIMESTAMPDIFF(DAY, CONCAT_WS('-', YEAR(st.date_game) - 1, '03',            '01'     ), st.date_game)
  ),
  IFNULL (
  TIMESTAMPDIFF(DAY, CONCAT_WS('-', YEAR(st.date_game), m.birth_month, m.birth_day), st.date_game),
  TIMESTAMPDIFF(DAY, CONCAT_WS('-', YEAR(st.date_game), '03',           '01'      ), st.date_game)
  )
  ), 3, '0'
  )
  )
  AS age,
  IF(assist1_id IS NOT NULL, CONCAT_WS(',', a1.name_last, a1.name_first), NULL) AS assist1_csk,
  career_goal_num,
  st.date_game AS date_game,
  SUBSTRING(time_elapsed,4) AS time_elapsed,
  CASE WHEN is_penalty_shot=1 THEN "PS" WHEN is_empty_net=1 THEN "EN" ELSE "" END AS ps_or_en,
  st.game_result AS game_result,
  strength,
  IF(assist2_id IS NOT NULL, CONCAT_WS(',', a2.name_last, a2.name_first), NULL) AS assist2_csk,
  IF(assist1_id IS NOT NULL, CONCAT(a1.name_first,' ',a1.name_last), NULL) AS assist1,
  IF(assist1_id IS NOT NULL, CONCAT('/players/', LEFT(assist1_id, 1), '/', assist1_id, '.html'), NULL) AS assist1_link,
  arena_name,
  st.team_id AS team_id,
  IF(goalie_id IS NOT NULL, CONCAT(gl.name_first,' ',gl.name_last), NULL) AS goalie,
  st.opp_id AS opp_id,
  IF(goalie_id IS NOT NULL, CONCAT_WS(',', gl.name_last, gl.name_first), NULL) AS goalie_csk,
  CONCAT('/players/', LEFT(scorer_id, 1), '/', scorer_id, '.html') AS scorer_link
FROM games_scoring
  LEFT JOIN master m ON scorer_id=m.player_id
  LEFT JOIN master a1 ON assist1_id=a1.player_id
  LEFT JOIN master a2 ON assist2_id=a2.player_id
  LEFT JOIN master gl ON goalie_id=gl.player_id
  JOIN teams_games st USING (game_id, year_id, team_id)
  LEFT JOIN arenas USING (arena_id, year_id)
WHERE st.is_playoffs = 0 AND  st.year_id>=2025 AND  st.year_id<=2025 AND games_scoring.year_id>=1918
ORDER BY date_game DESC, date_game DESC
LIMIT 0, 20


$VAR1 = [];