Devin Powers

Devin Powers

Engineering Portfolio.

NHL Database Design

16 minute read

Introduction: Scope/Goal of our Project

The National Hockey League is a professional ice hockey league in North America, currently comprising 32 teams: 24 in the United States (Go Wings) and 7 in Canada.

This project deals with building a database which includes all the details of players, teams and matches.

The goal of this project is to design a large NHL Database using an Entity–Relationship Model/Diagram. Another goal for this project is to work with large files (millions of rows) to build a database.

Analytical Questions we can answer (Using SQL)

• How many Goals are recorded each season? By who?

  • How had the most Assists each Season?

Files and Data Description

The data represents all the official metrics measured for each game in the NHL from 2012-2013 to 2018-2019 seasons From Kaggle!

Here’s the Link to the NHL Data from Kaggle

The Data includes the following files in csv form:

  • game_goalie_stats.csv
  • game_goals.csv
  • game_officals.csv
  • game_penalties.csv
  • game_plays_players.csv
  • game_plays.csv
  • game_scratches.csv
  • game_shifts.csv
  • game_skater_stats.csv
  • game_teams_stats.csv
  • game.csv
  • player_info.csv
  • team_info.csv

We can explore each of these .csv files and decide on which columns we want to include in our database design.

Some of these files and table in the database have millions of rows (up to 7 million)

Exlpore/Data Wrangling

Note: Some of these files have Duplicate rows, so you can either use Excel function to drop/delete duplicate records or use Pandas in Python to delete the duplicate rows.

Entity–Relationship Diagram

"insert image

Create Tables

Lets use our model above to create tables!

Team Information Table

CREATE TABLE team_information (
	team_id INT NOT NULL,
    franchise_id INT NOT NULL,
    short_name VARCHAR(40) NULL,
    team_name VARCHAR(40) NULL,
    abbreviation VARCHAR(5) NULL,
    link VARCHAR(100) NULL,
    Primary Key (team_id) --Primary Key
);

Player Information

CREATE TABLE player_information (
	player_id INT NOT NULL,
    first_name VARCHAR(40) NULL,
    last_name VARCHAR(40) NULL,
    nationality VARCHAR(5) NULL,
    birth_city VARCHAR(50) NULL,
    primary_position VARCHAR(5) NULL,
    birth_date DATE NULL,
    birth_state VARCHAR(4) NULL,
    height_cm  DECIMAL(6,2) NULL,
    weight_lb int NULL,
    shootsCatches VARCHAR(2) NULL,
    Primary Key (player_id)
);

Game Table

CREATE TABLE game (
	game_id INT NOT NULL,
    season INT NULL,
    game_type VARCHAR(2) NULL,
    date_time DATE NULL,
    date_time_GMT DATE NULL,
    away_team_id INT NULL,
    home_team_id INT NULL,
    away_goals INT NULL,
    home_goals INT NULL,
    outcome VARCHAR(20) NULL,
    home_rink_start_side VARCHAR(5) NULL,
    venue VARCHAR(50) NULL,
    PRIMARY KEY (game_id)
);

Game Plays Table

CREATE TABLE game_plays (
	play_id VARCHAR(20) NOT NULL,
	game_id INT NOT NULL,
    team_id_for INT NULL,
    team_id_against INT NULL,
    play_event VARCHAR(40) NULL,
    secondary_type VARCHAR(40) NULL,
    x INT NULL,
    y INT NULL,
    period INT NULL,
    period_type VARCHAR(10) NULL,
    period_time INT NULL,
    period_time_remaining INT NULL,
    date_time DATETIME NULL,
    goals_away INT NULL,
    goals_home INT NULL,
    play_description VARCHAR(200) NULL,
    st_x INT NULL,
    st_y INT NULL,
    PRIMARY KEY (play_id)
);

Game Plays Players Table

game_play_id is the Primary Key and Auto incremented!

CREATE TABLE game_plays_players (
    game_play_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	play_id VARCHAR(20) NOT NULL,
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_type VARCHAR(20) NULL
);

Game Shifts Table

shift_id is auto incremented Primary Key

CREATE TABLE game_shifts (
    shift_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	game_id INT NOT NULL,
    player_id INT NOT NULL,
    period INT NULL,
    shift_start INT NULL,
    shift_end INT NULL
);

Game Team Stats Table

  • Primary Key for this table is (game_id, team_id)
CREATE TABLE game_teams_stats (
	game_id INT NOT NULL,
    team_id INT NOT NULL,
    hoa VARCHAR(5) NULL,
    won VARCHAR(5) NULL,
    settled_in VARCHAR(5) NULL,
    head_coach VARCHAR(40) NULL,
    goals INT NULL,
    shots INT NULL,
    hits INT NULL,
    pim INT NULL,
    powerplay_opportunities INT NULL,
    powerplay_goals INT NULL,
    faceoff_win_percentage DECIMAL(8,5) NULL,
    giveaways INT NULL,
    takeaways INT NULL,
    blocked INT NULL,
    startRinkSide VARCHAR(6),
    CONSTRAINT PK_game_teams_stats PRIMARY KEY (game_id,team_id)
);

Game Skater Stats Table

  • Stats for regular players not goalies

Note: Primary key for this table is (game_id, player_id, team_id)

CREATE TABLE game_skater_stats (
	game_id INT NOT NULL,
    player_id INT NOT NULL,
    team_id INT NOT NULL,
    time_on_ice INT NULL,
    assists INT NULL,
    goals INT NULL,
    shots INT NULL,
    hits INT NULL,
    power_play_goals INT NULL,
    power_play_assists INT NULL,
    penality_minutes INT NULL,
    faceoff_wins INT NULL,
    faceoff_taken INT NULL,
    takeaways INT NULL,
    giveaways INT NULL,
    short_handed_goals INT NULL,
    short_handed_assists INT NULL,
    blocked INT NULL,
    plus_minus INT NULL,
    even_time_on_ice INT NULL,
    short_handed_time_on_ice INT NULL,
    powerplay_time_on_ice INT NULL,
    CONSTRAINT PK_game_skater_stats PRIMARY KEY (game_id, player_id, team_id)
);

Goalies Stats Table

  • Stats on Goalies
CREATE TABLE game_goalie_stats (
	game_id INT NOT NULL,
    player_id INT NOT NULL,
    team_id INT NOT NULL,
    time_on_ice INT NULL,
    assists INT NULL,
    goals INT NULL,
    pim INT NULL,
    shots INT NULL,
    saves INT NULL,
    power_play_saves INT NULL,
    short_handed_saves INT NULL,
    even_saves INT NULL,
    short_handed_shots_against INT NULL,
    even_shots_against INT NULL,
    powerplay_shots_against INT NULL,
    decision VARCHAR(1) NULL,
    save_percentage DECIMAL(8,5) NULL,
    powerplay_save_percentage DECIMAL(8,5) NULL,
    even_strength_save_percentage DECIMAL(8,5) NULL,
    CONSTRAINT PK_game_goalie_stats PRIMARY KEY (game_id, player_id, team_id)
);

Game Scratches Table

CREATE TABLE game_scratches (
    game_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT NOT NULL,
    CONSTRAINT PK_game_scratches PRIMARY KEY (game_id,team_id,player_id )
 );

Game Penalties Table

CREATE TABLE game_penalties(
    play_id	VARCHAR(20) NOT NULL,
    penalty_severity VARCHAR(45),
    penalty_minutes INT NOT NULL,
    CONSTRAINT PK_game_penalties PRIMARY KEY(play_id,penalty_severity,penalty_minutes)
);

Game Officials

CREATE TABLE game_officials(
    game_id INT NOT NULL,
    official_name VARCHAR(60),
    official_type VARCHAR(20),
    CONSTRAINT PK_game_officials PRIMARY KEY(game_id,official_name)
);

Import Data into Tables

Many ways to import Data from .csv files into MySQL database. Can you import data function in MySQL WorkBench called, import Wizard, but that method can be slow for data with over 100,000+ rows

Other Ways: Write a script to load data in MySQL

load_nhl_tables.sql

  • This method, will import CSV file from our directory and import it into our table (team_information) and will ignore the first line which is the header! ```sql SET GLOBAL local_infile = ON;

USE nhl_blog;

LOAD DATA LOCAL INFILE ‘nhl-game-data/team_info.csv’
INTO TABLE team_information
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;


Then run this command to load all the data into our tables in our NHL Database

```bash
mysql -u root -p < load_nhl_tables.sql

Updating Relations in the Database

We can update the relations (foregin Keys and Primary Keys ) in the database after we’ve created the tables and imported some data!

Run these commands to update Relations:


USE NHL_blog_backup;

ALTER TABLE game_plays ADD CONSTRAINT fk_game_id_gp_g FOREIGN KEY (game_id) 
REFERENCES game(game_id);


-- Worked
ALTER TABLE game_plays_players ADD CONSTRAINT fk_game_id_gpp_g FOREIGN KEY (game_id) 
REFERENCES game(game_id);

-- Worked
ALTER TABLE game_shifts ADD CONSTRAINT fk_game_id_gs_g FOREIGN KEY (game_id) 
REFERENCES game(game_id);

-- Worked
ALTER TABLE game_teams_stats ADD CONSTRAINT fk_game_id_gts_g FOREIGN KEY (game_id) 
REFERENCES game(game_id);

-- Worked
ALTER TABLE game_skater_stats ADD CONSTRAINT fk_game_id_gss_g FOREIGN KEY (game_id) 
REFERENCES game(game_id);

-- Worked
ALTER TABLE game_goalie_stats ADD CONSTRAINT fk_game_id_ggs_g FOREIGN KEY (game_id) 
REFERENCES game(game_id);

-- worked
ALTER TABLE game_plays_players ADD CONSTRAINT fk_player_id_gpp_pi FOREIGN KEY (player_id) 
REFERENCES player_information(player_id);

-- worked
ALTER TABLE game_shifts ADD CONSTRAINT fk_player_id_gs_pi FOREIGN KEY (player_id) 
REFERENCES player_information(player_id);


-- Worked
ALTER TABLE game_skater_stats ADD CONSTRAINT fk_player_id_gss_pi FOREIGN KEY (player_id) 
REFERENCES player_information(player_id);

-- Worked
ALTER TABLE game_goalie_stats ADD CONSTRAINT fk_player_id_ggs_pi FOREIGN KEY (player_id) 
REFERENCES player_information(player_id);

-- doesnt work :/
ALTER TABLE game_teams_stats ADD CONSTRAINT fk_team_id_gts_ti FOREIGN KEY (team_id) 
REFERENCES team_information(team_id);
-- doesnt work :/
ALTER TABLE game_skater_stats ADD CONSTRAINT fk_team_id_gss_ti FOREIGN KEY (team_id) 
REFERENCES team_information(team_id);
-- doesnt work :/
ALTER TABLE game_goalie_stats ADD CONSTRAINT fk_team_id_ggs_ti FOREIGN KEY (team_id) 
REFERENCES team_information(team_id);

-- Game Scratches (WORKS!!!!)
ALTER TABLE game_scratches ADD CONSTRAINT fk_team_id_ FOREIGN KEY (game_id) 
REFERENCES game(game_id);

-- Works
ALTER TABLE game_penalties ADD CONSTRAINT fk_game_ FOREIGN KEY (play_id) 
REFERENCES game_plays(play_id);

-- Works

ALTER TABLE game_officials ADD CONSTRAINT fk_game_ref_ FOREIGN KEY (game_id) 
REFERENCES game(game_id);

Exploring Database

There’s millions of rows in certain tables in our database, lets explore exactly how many.

game_plays Table

select count(*) from game_plays;

Output:

+----------+
| count(*) |
+----------+
|  4217063 |
+----------+

game_plays_players Table

select count(*) from game_plays_players;

Output:

+----------+
| count(*) |
+----------+
|  7586604 |
+----------+

team_information Table

select * from team_information;

Output:

+---------+--------------+--------------+----------------+--------------+------------------+
| team_id | franchise_id | short_name   | team_name      | abbreviation | link             |
+---------+--------------+--------------+----------------+--------------+------------------+
|       1 |           23 | New Jersey   | Devils         | NJD          | /api/v1/teams/1  |
|       2 |           22 | NY Islanders | Islanders      | NYI          | /api/v1/teams/2  |
|       3 |           10 | NY Rangers   | Rangers        | NYR          | /api/v1/teams/3  |
|       4 |           16 | Philadelphia | Flyers         | PHI          | /api/v1/teams/4  |
|       5 |           17 | Pittsburgh   | Penguins       | PIT          | /api/v1/teams/5  |
|       6 |            6 | Boston       | Bruins         | BOS          | /api/v1/teams/6  |
|       7 |           19 | Buffalo      | Sabres         | BUF          | /api/v1/teams/7  |
|       8 |            1 | Montreal     | Canadiens      | MTL          | /api/v1/teams/8  |
|       9 |           30 | Ottawa       | Senators       | OTT          | /api/v1/teams/9  |
|      10 |            5 | Toronto      | Maple Leafs    | TOR          | /api/v1/teams/10 |
|      11 |           35 | Atlanta      | Thrashers      | ATL          | /api/v1/teams/11 |
|      12 |           26 | Carolina     | Hurricanes     | CAR          | /api/v1/teams/12 |
|      13 |           33 | Florida      | Panthers       | FLA          | /api/v1/teams/13 |
|      14 |           31 | Tampa Bay    | Lightning      | TBL          | /api/v1/teams/14 |
|      15 |           24 | Washington   | Capitals       | WSH          | /api/v1/teams/15 |
|      16 |           11 | Chicago      | Blackhawks     | CHI          | /api/v1/teams/16 |
|      17 |           12 | Detroit      | Red Wings      | DET          | /api/v1/teams/17 |
|      18 |           34 | Nashville    | Predators      | NSH          | /api/v1/teams/18 |
|      19 |           18 | St Louis     | Blues          | STL          | /api/v1/teams/19 |
|      20 |           21 | Calgary      | Flames         | CGY          | /api/v1/teams/20 |
|      21 |           27 | Colorado     | Avalanche      | COL          | /api/v1/teams/21 |
|      22 |           25 | Edmonton     | Oilers         | EDM          | /api/v1/teams/22 |
|      23 |           20 | Vancouver    | Canucks        | VAN          | /api/v1/teams/23 |
|      24 |           32 | Anaheim      | Ducks          | ANA          | /api/v1/teams/24 |
|      25 |           15 | Dallas       | Stars          | DAL          | /api/v1/teams/25 |
|      26 |           14 | Los Angeles  | Kings          | LAK          | /api/v1/teams/26 |
|      27 |           28 | Phoenix      | Coyotes        | PHX          | /api/v1/teams/27 |
|      28 |           29 | San Jose     | Sharks         | SJS          | /api/v1/teams/28 |
|      29 |           36 | Columbus     | Blue Jackets   | CBJ          | /api/v1/teams/29 |
|      30 |           37 | Minnesota    | Wild           | MIN          | /api/v1/teams/30 |
|      52 |           35 | Winnipeg     | Jets           | WPG          | /api/v1/teams/52 |
|      53 |           28 | Arizona      | Coyotes        | ARI          | /api/v1/teams/53 |
|      54 |           38 | Vegas        | Golden Knights | VGK          | /api/v1/teams/54 |
+---------+--------------+--------------+----------------+--------------+------------------+

Create Views on our NHL Database

Hits View

CREATE OR REPLACE VIEW hits AS
SELECT game_plays.*,
		game_plays_players.player_id,
		player_information.first_name,
		player_information.last_name,
		player_information.primary_position
FROM game_plays
LEFT JOIN game_plays_players ON game_plays.play_id = game_plays_players.play_id
LEFT JOIN player_information ON game_plays_players.player_id = player_information.player_id
WHERE game_plays.play_event = "Hit" AND game_plays_players.playerType = "Hitter";

Player Hits View

CREATE OR REPLACE VIEW player_hits AS
SELECT SUBSTRING(game_id, 1, 4) AS season,
	player_id,
	COUNT(*) AS hit_count
FROM hits
GROUP BY season, player_id;

Lets see some of the output from this view above

SELECT * FROM player_hits
LIMIT 10;

Output:

+--------+-----------+-----------+
| season | player_id | hit_count |
+--------+-----------+-----------+
| 2010   |   8446485 |        13 |
| 2010   |   8449645 |         1 |
| 2010   |   8450725 |        29 |
| 2010   |   8456283 |         7 |
| 2010   |   8457063 |        10 |
| 2010   |   8458361 |         1 |
| 2010   |   8458520 |         1 |
| 2010   |   8458525 |        15 |
| 2010   |   8458529 |         5 |
| 2010   |   8458537 |         6 |
+--------+-----------+-----------+

Beats View

Goals View

CREATE OR REPLACE VIEW goals AS
SELECT gp.*,
	gpp.player_id, 
    pi.first_name, 
    pi.last_name, 
    pi.primary_position, 
    pi.birth_date
FROM game_plays AS gp
LEFT JOIN game_plays_players AS gpp
	USING (play_id)
LEFT JOIN player_information AS pi
	USING (player_id)
WHERE gp.play_event = "Goal"
	AND gpp.playerType = "Scorer";

Player Goal for Season

CREATE OR REPLACE VIEW player_goals AS
SELECT SUBSTRING(game_id, 1, 4) AS season,
	player_id,
	COUNT(*) AS goal_count
FROM goals
GROUP BY season, 
	player_id;

Lets see this in action

select * from player_goals
LIMIT 10;

Output:

+--------+-----------+------------+
| season | player_id | goal_count |
+--------+-----------+------------+
| 2016   |   8474141 |         35 |
| 2016   |   8477929 |          4 |
| 2016   |   8478550 |         35 |
| 2016   |   8466148 |         26 |
| 2016   |   8476177 |          9 |
| 2016   |   8476461 |         14 |
| 2016   |   8474190 |         32 |
| 2016   |   8473573 |         22 |
| 2017   |   8476878 |          7 |
| 2017   |   8476483 |         35 |
+--------+-----------+------------+

What is the distribution of yearly hit totals per team?

SELECT *,
    RANK() OVER (
		PARTITION BY season
        ORDER BY hit_count DESC
    ) AS season_rank
FROM (
	SELECT SUBSTRING(h.game_id, 1, 4) AS season,
		h.team_id_for, 
		ti.short_name,
		ti.team_name,
		COUNT(*) AS hit_count
	FROM hits AS h
	LEFT JOIN team_information AS ti
		ON h.team_id_for = ti.team_id
	GROUP BY season, 
		h.team_id_for
) AS team_hits
LIMIT 10;

Just wanna see first 10.

Output:

+--------+-------------+-------------+--------------+-----------+-------------+
| season | team_id_for | short_name  | team_name    | hit_count | season_rank |
+--------+-------------+-------------+--------------+-----------+-------------+
| 2010   |          23 | Vancouver   | Canucks      |      2605 |           1 |
| 2010   |           5 | Pittsburgh  | Penguins     |      2480 |           2 |
| 2010   |           3 | NY Rangers  | Rangers      |      2468 |           3 |
| 2010   |           6 | Boston      | Bruins       |      2391 |           4 |
| 2010   |          29 | Columbus    | Blue Jackets |      2293 |           5 |
| 2010   |          26 | Los Angeles | Kings        |      2289 |           6 |
| 2010   |          28 | San Jose    | Sharks       |      2255 |           7 |
| 2010   |          25 | Dallas      | Stars        |      2189 |           8 |
| 2010   |          15 | Washington  | Capitals     |      2136 |           9 |
| 2010   |          12 | Carolina    | Hurricanes   |      2128 |          10 |
+--------+-------------+-------------+--------------+-----------+-------------+

Who is taking the most hits?

SELECT b.player_id,
	pi.first_name,
    pi.last_name,
    pi.primary_position,
	COUNT(*) AS beat_count
FROM beats AS b
LEFT JOIN player_information AS pi
	USING(player_id)
GROUP BY player_id
ORDER BY beat_count DESC
LIMIT 10;

Output:

+-----------+------------+-------------+------------------+------------+
| player_id | first_name | last_name   | primary_position | beat_count |
+-----------+------------+-------------+------------------+------------+
|   8471702 | Matt       | Niskanen    | D                |       2158 |
|   8470187 | Johnny     | Boychuk     | D                |       2023 |
|   8474027 | Justin     | Braun       | D                |       1942 |
|   8474565 | Alex       | Pietrangelo | D                |       1901 |
|   8471887 | Patric     | Hornqvist   | RW               |       1878 |
|   8471303 | Alexander  | Edler       | D                |       1816 |
|   8468508 | Justin     | Williams    | RW               |       1791 |
|   8471769 | Niklas     | Hjalmarsson | D                |       1759 |
|   8475179 | Dmitry     | Kulikov     | D                |       1752 |
|   8473604 | Jonathan   | Toews       | C                |       1744 |
+-----------+------------+-------------+------------------+------------+

How many goals are recorded each season?

SELECT SUBSTRING(game_id, 1, 4) AS season,
	COUNT(*) AS goal_count
FROM goals
GROUP BY season;

Output:

+--------+------------+
| season | goal_count |
+--------+------------+
| 2016   |       7251 |
| 2017   |       7992 |
| 2015   |       7082 |
| 2014   |       7243 |
| 2013   |       7377 |
| 2012   |       4411 |
| 2011   |       7233 |
| 2010   |       7376 |
| 2009   |       7149 |
| 2008   |       7247 |
| 2007   |       6900 |
| 2006   |       7365 |
| 2002   |       6327 |
| 2003   |       6201 |
| 2005   |       7617 |
| 2000   |       6633 |
| 2001   |       6266 |
| 2019   |      14770 |
| 2018   |      16552 |
+--------+------------+

What is the distribution of yearly goal totals per team?

SELECT *,
    RANK() OVER (
		PARTITION BY season
        ORDER BY goal_count DESC
    ) season_rank
FROM (
	SELECT SUBSTRING(g.game_id, 1, 4) AS season,
		g.team_id_for, 
		ti.short_name,
		ti.team_name,
		COUNT(*) AS goal_count
	FROM goals AS g
	LEFT JOIN team_information AS ti
		ON g.team_id_for = ti.team_id
	GROUP BY season, 
		g.team_id_for
) AS team_goals
LIMIT 10;

Output:

+--------+-------------+-------------+-----------+------------+-------------+
| season | team_id_for | short_name  | team_name | goal_count | season_rank |
+--------+-------------+-------------+-----------+------------+-------------+
| 2000   |           1 | New Jersey  | Devils    |        293 |           1 |
| 2000   |           5 | Pittsburgh  | Penguins  |        271 |           2 |
| 2000   |          21 | Colorado    | Avalanche |        263 |           3 |
| 2000   |           9 | Ottawa      | Senators  |        258 |           4 |
| 2000   |          17 | Detroit     | Red Wings |        253 |           5 |
| 2000   |          26 | Los Angeles | Kings     |        252 |           6 |
| 2000   |           3 | NY Rangers  | Rangers   |        248 |           7 |
| 2000   |          19 | St Louis    | Blues     |        243 |           8 |
| 2000   |          22 | Edmonton    | Oilers    |        238 |           9 |
| 2000   |          25 | Dallas      | Stars     |        237 |          10 |
+--------+-------------+-------------+-----------+------------+-------------+

WHo’s scored the msot Goals the past seasons

  • We can break down this Problem

1.Find top goal counts from seasons

SELECT season,MAX(goal_count) AS goal_count
FROM player_goals
GROUP BY season;

Output:

+--------+------------+
| season | goal_count |
+--------+------------+
| 2016   |         52 |
| 2015   |         55 |
| 2017   |         64 |
| 2014   |         60 |
| 2013   |         51 |
| 2012   |         38 |
| 2011   |         61 |
| 2010   |         51 |
| 2007   |         66 |
| 2000   |         59 |
| 2001   |         52 |
| 2006   |         54 |
| 2009   |         59 |
| 2002   |         45 |
| 2008   |         56 |
| 2003   |         41 |
| 2005   |         58 |
| 2019   |        112 |
| 2018   |        114 |
+--------+------------+

SELECT pg2.season, 
	pg2.player_id, 
	pi.first_name, 
    pi.last_name, 
    pi.primary_position, 
    pg2.goal_count
FROM (
	SELECT season, 
		MAX(goal_count) AS goal_count
    FROM player_goals 
    GROUP BY season
) AS pg1 
JOIN player_goals AS pg2 
	USING (season, 
		goal_count)
LEFT JOIN player_information AS pi
	USING (player_id)

ORDER BY season;

SELECT pg1.season, 
	pg1.player_id, 
	pi.first_name, 
    pi.last_name, 
    pi.primary_position, 
    pg1.goal_count
FROM (
	SELECT season, 
		MAX(goal_count) AS goal_count
    FROM player_goals 
    GROUP BY season
) AS pg1 

LEFT JOIN player_information AS pi
	USING (player_id)

ORDER BY season;

Output:

+--------+-----------+------------+------------+------------------+------------+
| season | player_id | first_name | last_name  | primary_position | goal_count |
+--------+-----------+------------+------------+------------------+------------+
| 2000   |   8455738 | Pavel      | Bure       | RW               |         59 |
| 2001   |   8462042 | Jarome     | Iginla     | RW               |         52 |
| 2002   |   8459444 | Todd       | Bertuzzi   | RW               |         45 |
| 2003   |   8470041 | Rick       | Nash       | LW               |         41 |
| 2003   |   8462042 | Jarome     | Iginla     | RW               |         41 |
| 2003   |   8469454 | Ilya       | Kovalchuk  | RW               |         41 |
| 2005   |   8471214 | Alex       | Ovechkin   | LW               |         58 |
| 2006   |   8467329 | Vincent    | Lecavalier | C                |         54 |
| 2007   |   8471214 | Alex       | Ovechkin   | LW               |         66 |
| 2008   |   8471214 | Alex       | Ovechkin   | LW               |         56 |
| 2009   |   8471675 | Sidney     | Crosby     | C                |         59 |
| 2010   |   8467875 | Daniel     | Sedin      | LW               |         51 |
| 2011   |   8471215 | Evgeni     | Malkin     | C                |         61 |
| 2012   |   8474141 | Patrick    | Kane       | RW               |         38 |
| 2013   |   8471214 | Alex       | Ovechkin   | LW               |         51 |
| 2014   |   8471214 | Alex       | Ovechkin   | LW               |         60 |
| 2015   |   8470794 | Joe        | Pavelski   | C                |         55 |
| 2016   |   8471675 | Sidney     | Crosby     | C                |         52 |
| 2017   |   8471214 | Alex       | Ovechkin   | LW               |         64 |
| 2018   |   8471214 | Alex       | Ovechkin   | LW               |        114 |
| 2019   |   8477956 | David      | Pastrnak   | RW               |        112 |
+--------+-----------+------------+------------+------------------+------------+

RIP the NHL Lockout in 2004 :/

You may also enjoy