Hockey (SQL) - myCompiler

Fork
CREATE TABLE team (
	  team_id INTEGER PRIMARY KEY AUTOINCREMENT,
	  name TEXT NOT NULL,
	  location TEXT NOT NULL,
	  abbreviation TEXT NOT NULL
	);

CREATE TABLE season (
  season_id INTEGER PRIMARY KEY AUTOINCREMENT,
  year VARCHAR(4) NOT NULL UNIQUE
);

CREATE TABLE player (
  player_id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  age INTEGER NOT NULL,
  position TEXT CHECK(position IN ('LW', 'C', 'RW', 'LD', 'RD', 'G') ) NOT NULL,
  number INTEGER,
  nationality TEXT,
  height_cm REAL,
  weight_kg REAL,
  shoots TEXT CHECK(shoots IN ('Left', 'Right')) NOT NULL
);

CREATE TABLE player_season_stats (
  stat_id INTEGER PRIMARY KEY AUTOINCREMENT,
  player_id INTEGER,
  season_id INTEGER,
  team_id INTEGER,
  goals INTEGER DEFAULT 0,
  assists INTEGER DEFAULT 0,
  points INTEGER DEFAULT 0,
  plus_minus INTEGER DEFAULT 0,
  penalty_minutes INTEGER DEFAULT 0,
  power_play_goals INTEGER DEFAULT 0,
  short_handed_goals INTEGER DEFAULT 0,
  game_winning_goals INTEGER DEFAULT 0,
  shots_on_goal INTEGER DEFAULT 0,
  shot_percentage REAL DEFAULT 0.0,
  time_on_ice INTEGER DEFAULT 0,
  hits INTEGER DEFAULT 0,
  blocks INTEGER DEFAULT 0,
  giveaways INTEGER DEFAULT 0,
  takeaways INTEGER DEFAULT 0,
  faceoff_percentage REAL DEFAULT 0.0,
  save_percentage REAL DEFAULT 0.0,
  goals_against_average REAL DEFAULT 0.0,
  FOREIGN KEY (player_id) REFERENCES player(player_id),
  FOREIGN KEY (season_id) REFERENCES season(season_id),
  FOREIGN KEY (team_id) REFERENCES team(team_id)
);

CREATE TABLE game (
  game_id INTEGER PRIMARY KEY AUTOINCREMENT,
  season_id INTEGER NOT NULL,
  game_start_datetime TEXT NOT NULL,
  game_end_datetime TEXT NOT NULL,
  home_team INTEGER NOT NULL,
  away_team INTEGER NOT NULL,
  home_goals INTEGER NOT NULL,
  away_goals INTEGER NOT NULL,
  game_type TEXT CHECK(game_type IN ('REGULATION', 'OVERTIME', 'SHOOTOUT', 'PRESEASON') ) NOT NULL,
  playoff_round TEXT CHECK(playoff_round IN ('Round1', 'Quarterfinals', 'Semifinals', 'Finals', 'Stanley Cup Final')),
  home_shots INTEGER DEFAULT 0,
  away_shots INTEGER DEFAULT 0,
  home_penalty_minutes INTEGER DEFAULT 0,
  away_penalty_minutes INTEGER DEFAULT 0,
  home_power_play_goals INTEGER DEFAULT 0,
  away_power_play_goals INTEGER DEFAULT 0,
  home_short_handed_goals INTEGER DEFAULT 0,
  away_short_handed_goals INTEGER DEFAULT 0,
  overtime_period INTEGER DEFAULT 0,
  shootout_goals_home INTEGER DEFAULT 0,
  shootout_goals_away INTEGER DEFAULT 0,
  venue TEXT,
  attendance INTEGER DEFAULT 0,
  FOREIGN KEY (season_id) REFERENCES season(season_id),
  FOREIGN KEY (home_team) REFERENCES team(team_id),
  FOREIGN KEY (away_team) REFERENCES team(team_id)
);

CREATE TABLE player_game (
  player_id INTEGER NOT NULL,
  game_id INTEGER NOT NULL,
  time_on_ice TEXT NOT NULL,
  goals INTEGER,
  assists INTEGER,
  shots INTEGER,
  hits INTEGER,
  blocks INTEGER,
  saves INTEGER,
  goals_allowed INTEGER,
  PRIMARY KEY (player_id, game_id),
  FOREIGN KEY (player_id) REFERENCES player(player_id),
  FOREIGN KEY (game_id) REFERENCES game(game_id)
);

INSERT INTO team (name, location, abbreviation) VALUES 
('Toronto Maple Leafs', 'Toronto, ON', 'TOR'),
('Montreal Canadiens', 'Montreal, QC', 'MTL'),
('Boston Bruins', 'Boston, MA', 'BOS'),
('Detroit Red Wings', 'Detroit, MI', 'DET'),
('New York Rangers', 'New York, NY', 'NYR'),
('Pittsburgh Penguins', 'Pittsburgh, PA', 'PIT'),
('Edmonton Oilers', 'Edmonton, AB', 'EDM'),
('Vancouver Canucks', 'Vancouver, BC', 'VAN'),
('Calgary Flames', 'Calgary, AB', 'CGY'),
('Winnipeg Jets', 'Winnipeg, MB', 'WPG');

INSERT INTO season (year) VALUES 
('2022-2023'),
('2021-2022'),
('2020-2021');

INSERT INTO player (first_name, last_name, age, position, number, nationality, height_cm, weight_kg, shoots) VALUES 
('Auston', 'Matthews', 24, 'C', 34, 'USA', 188.0, 91.0, 'Right'),
('Connor', 'McDavid', 25, 'C', 97, 'CAN', 183.0, 88.0, 'Left'),
('Nathan', 'Mackinnon', 26, 'C', 29, 'CAN', 180.0, 86.0, 'Right'),
('Alex', 'Ovechkin', 36, 'LW', 8, 'RUS', 188.0, 100.0, 'Left'),
('Sidney', 'Crosby', 34, 'C', 87, 'CAN', 180.0, 91.0, 'Left'),
('Ilya', 'Mikheyev', 27, 'LW', 65, 'RUS', 188.0, 88.0, 'Left'),
('William', 'Nylander', 25, 'RW', 29, 'SWE', 183.0, 86.0, 'Right'),
('John', 'Taveras', 31, 'C', 91, 'CAN', 185.0, 93.0, 'Left'),
('Victor', 'Hedman', 31, 'LD', 77, 'SWE', 193.0, 98.0, 'Left'),
('Cale', 'Makar', 23, 'LD', 8, 'CAN', 183.0, 84.0, 'Left'),
('Andrei', 'Vasilevskiy', 27, 'G', 88, 'RUS', 193.0, 98.0, 'Left'),
('Carter', 'Hart', 23, 'G', 79, 'CAN', 185.0, 87.0, 'Right'),
('Roman', 'Josi', 31, 'RD', 59, 'SUI', 183.0, 88.0, 'Right'),
('Connor', ' Hellebuyck', 28, 'G', 37, 'USA', 191.0, 96.0, 'Right'),
('Leon', 'Draisaitl', 26, 'C', 29, 'GER', 188.0, 99.0, 'Left');

INSERT INTO player_season_stats 
(player_id, season_id, team_id, goals, assists, points, plus_minus, penalty_minutes, power_play_goals, short_handed_goals, game_winning_goals, shots_on_goal, shot_percentage, time_on_ice, hits, blocks, giveaways, takeaways, faceoff_percentage, save_percentage, goals_against_average) 
VALUES 
(1, 1, 1, 60, 46, 106, 30, 20, 15, 2, 10, 350, 0.171, 1500, 50, 30, 50, 80, NULL, NULL, NULL),
(2, 1, 2, 50, 70, 120, 25, 15, 12, 1, 8, 300, 0.167, 1600, 40, 50, 40, 70, NULL, NULL, NULL),
(3, 1, 3, 45, 55, 100, 15, 25, 10, 3, 6, 280, 0.161, 1450, 60, 40, 60, 60, NULL, NULL, NULL),
(4, 1, 1, 55, 35, 90, 20, 30, 18, 1, 9, 320, 0.172, NULL, NULL, NULL, NULL, NULL, 0.925, 2.30, NULL),
(5, 1, 2, 30, 40, 70, 10, 20, 8, 1, 4, 250, 0.120, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(6, 1, 3, 25, 30, 55, 5, 15, 5, 0, 3, 200, 0.125, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(7, 1, 1, 35, 40, 75, 20, 10, 10, 1, 5, 220, 0.159, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(8, 1, 2, 15, 35, 50, 10, 15, 5, 0, 2, 180, 0.083, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, 1, 3, 10, 40, 50, 5, 10, 3, 0, 1, 150, 0.067, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(10, 1, 1, 40, 25, 65, 15, 25, 12, 1, 6, 200, 0.200, 2000, 80, 60, 30, 50, NULL, NULL, NULL),
(11, 1, 2, 35, 20, 55, 10, 15, 7, 0, 3, 180, 0.194, 1800, 60, 50, 40, 60, NULL, NULL, NULL),
(12, 1, 3, 25, 35, 60, 5, 10, 5, 0, 2, 160, 0.156, 1700, 50, 40, 50, 70, NULL, NULL, NULL),
(13, 1, 1, 50, 20, 70, 20, 15, 15, 2, 5, 250, 0.200, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(14, 1, 2, 40, 30, 70, 15, 20, 10, 1, 4, 200, 0.200, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(1, 2, 1, 55, 40, 95, 25, 18, 12, 1, 9, 300, 0.183, 1550, 45, 35, 45, 75, NULL, NULL, NULL),
(2, 2, 2, 45, 65, 110, 20, 12, 15, 2, 7, 280, 0.161, 1650, 35, 45, 35, 65, NULL, NULL, NULL),
(3, 2, 3, 40, 50, 90, 10, 28, 8, 2, 5, 250, 0.160, 1400, 55, 45, 55, 55, NULL, NULL, NULL),
(4, 2, 1, 50, 30, 80, 15, 35, 15, 1, 8, 300, 0.167, NULL, NULL, NULL, NULL, NULL, 0.910, 2.40, NULL),
(5, 2, 2, 25, 35, 60, 5, 18, 6, 0, 2, 220, 0.114, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(6, 2, 3, 20, 25, 45, 0, 12, 3, 0, 1, 180, 0.111, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(7, 2, 1, 30, 35, 65, 15, 10, 8, 1, 4, 200, 0.150, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(8, 2, 2, 10, 30, 40, -5, 15, 2, 0, 1, 150, 0.067, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, 2, 3, 5, 25, 30, -10, 8, 1, 0, 0, 100, 0.050, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(10, 2, 1, 35, 25, 60, 10, 20, 10, 1, 4, 180, 0.194, 1900, 70, 60, 35, 45, NULL, NULL, NULL),
(11, 2, 2, 30, 15, 45, 5, 15, 5, 0, 2, 150, 0.200, 1750, 55, 50, 45, 55, NULL, NULL, NULL),
(12, 2, 3, 20, 30, 50, -5, 10, 3, 0, 1, 140, 0.143, 1600, 45, 40, 50, 60, NULL, NULL, NULL),
(13, 2, 1, 45, 15, 60, 15, 20, 12, 1, 5, 220, 0.205, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(14, 2, 2, 35, 25, 60, 10, 18, 8, 1, 3, 180, 0.194, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(1, 3, 1, 50, 45, 95, 20, 25, 15, 2, 8, 320, 0.156, 1450, 55, 35, 55, 75, NULL, NULL, NULL),
(2, 3, 2, 40, 60, 100, 15, 15, 10, 1, 6, 250, 0.160, 1600, 40, 50, 45, 65, NULL, NULL, NULL),
(3, 3, 3, 35, 45, 80, 5, 20, 7, 1, 4, 220, 0.159, 1400, 50, 45, 55, 55, NULL, NULL, NULL),
(4, 3, 1, 45, 30, 75, 10, 30, 12, 1, 7, 280, 0.161, NULL, NULL, NULL, NULL, NULL, 0.905, 2.50, NULL),
(5, 3, 2, 20, 35, 55, -5, 15, 5, 0, 1, 180, 0.111, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(6, 3, 3, 15, 25, 40, -10, 10, 2, 0, 0, 140, 0.107, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(7, 3, 1, 25, 35, 60, 10, 12, 6, 1, 3, 180, 0.139, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(8, 3, 2, 5, 20, 25, -15, 10, 1, 0, 0, 100, 0.050, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, 3, 3, 10, 30, 40, -5, 8, 2, 0, 1, 120, 0.083, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(10, 3, 1, 30, 20, 50, 5, 25, 8, 1, 3, 150, 0.200, 1800, 65, 55, 35, 45, NULL, NULL, NULL),
(11, 3, 2, 25, 15, 40, 0, 15, 4, 0, 1, 140, 0.190, 1300, 40, 50, 28, 60, NULL, NULL, NULL),
(12, 3, 3, 20, 30, 50, -5, 10, 3, 0, 1, 120, 0.167, 1500, 45, 40, 45, 65, NULL, NULL, NULL),
(13, 3, 1, 40, 20, 60, 15, 20, 12, 1, 5, 240, 0.167, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(14, 3, 2, 35, 25, 60, 10, 18, 8, 1, 3, 180, 0.194, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO game (season_id, game_start_datetime, game_end_datetime, home_team, away_team, home_goals, away_goals, game_type, playoff_round, home_shots, away_shots, home_penalty_minutes, away_penalty_minutes, home_power_play_goals, away_power_play_goals, home_short_handed_goals, away_short_handed_goals, overtime_period, shootout_goals_home, shootout_goals_away, venue, attendance) VALUES 
(1, '2023-01-01 19:00:00', '2023-01-01 22:00:00', 1, 2, 5, 3, 'REGULATION', NULL, 35, 30, 10, 12, 2, 1, 0, 0, 0, 0, 0, 'Arena 1', 18000),
(1, '2023-01-02 19:30:00', '2023-01-02 22:30:00', 3, 1, 2, 4, 'REGULATION', NULL, 25, 35, 8, 10, 1, 2, 0, 0, 0, 0, 0, 'Arena 2', 16500),
(1, '2023-01-03 19:00:00', '2023-01-03 21:45:00', 2, 4, 3, 2, 'OVERTIME', NULL, 30, 25, 12, 8, 1, 1, 0, 0, 1, 0, 0, 'Arena 3', 17800),
(1, '2023-01-04 19:30:00', '2023-01-04 22:15:00', 4, 3, 4, 3, 'REGULATION', NULL, 30, 28, 10, 14, 2, 1, 0, 0, 0, 0, 0, 'Arena 4', 15200),
(1, '2023-01-05 19:00:00', '2023-01-05 21:45:00', 1, 5, 6, 2, 'REGULATION', NULL, 40, 20, 6, 10, 3, 0, 0, 0, 0, 0, 0, 'Arena 1', 19200),
(1, '2023-01-06 19:30:00', '2023-01-06 22:05:00', 5, 2, 3, 5, 'REGULATION', NULL, 25, 35, 14, 10, 1, 2, 0, 0, 0, 0, 0, 'Arena 5', 17500),
(1, '2023-01-07 19:00:00', '2023-01-07 21:45:00', 3, 1, 2, 3, 'SHOOTOUT', NULL, 35, 30, 12, 10, 1, 1, 0, 0, 0, 1, 2, 'Arena 2', 18500),
(1, '2023-01-08 19:30:00', '2023-01-08 22:05:00', 2, 4, 4, 2, 'REGULATION', NULL, 30, 25, 8, 12, 2, 0, 0, 0, 0, 0, 0, 'Arena 3', 17000),
(1, '2023-01-09 19:00:00', '2023-01-09 21:45:00', 4, 3, 3, 4, 'OVERTIME', NULL, 35, 30, 10, 8, 1, 2, 0, 0, 1, 0, 1, 'Arena 4', 16200),
(1, '2023-01-10 19:30:00', '2023-01-10 22:15:00', 1, 2, 5, 2, 'REGULATION', NULL, 30, 25, 10, 12, 2, 1, 0, 0, 0, 0, 0, 'Arena 1', 18300),

(2, '2022-01-01 19:00:00', '2022-01-01 21:45:00', 1, 3, 4, 2, 'REGULATION', NULL, 30, 25, 10, 12, 2, 1, 0, 0, 0, 0, 0, 'Arena 1', 17500),
(2, '2022-01-02 19:30:00', '2022-01-02 22:15:00', 4, 2, 3, 4, 'REGULATION', NULL, 35, 30, 8, 10, 1, 2, 0, 0, 0, 0, 0, 'Arena 4', 16800),
(2, '2022-01-03 19:00:00', '2022-01-03 22:00:00', 2, 5, 5, 3, 'OVERTIME', NULL, 35, 30, 12, 10, 2, 1, 0, 0, 1, 0, 1, 'Arena 2', 18000),
(2, '2022-01-04 19:30:00', '2022-01-04 22:00:00', 5, 1, 2, 4, 'REGULATION', NULL, 30, 25, 10, 8, 1, 1, 0, 0, 0, 0, 0, 'Arena 5', 17200),
(2, '2022-01-05 19:00:00', '2022-01-05 21:45:00', 3, 4, 3, 2, 'REGULATION', NULL, 25, 30, 14, 10, 1, 1, 0, 0, 0, 0, 0, 'Arena 3', 16000),
(2, '2022-01-06 19:30:00', '2022-01-06 22:15:00', 1, 2, 4, 3, 'REGULATION', NULL, 35, 30, 8, 12, 2, 1, 0, 0, 0, 0, 0, 'Arena 1', 18500),
(2, '2022-01-07 19:00:00', '2022-01-07 21:45:00', 4, 3, 2, 3, 'REGULATION', NULL, 30, 25, 10, 8, 1, 1, 0, 0, 0, 0, 0, 'Arena 4', 15800),
(2, '2022-01-08 19:30:00', '2022-01-08 22:15:00', 2, 5, 5, 2, 'REGULATION', NULL, 35, 30, 12, 10, 2, 1, 0, 0, 0, 0, 0, 'Arena 2', 17800),
(2, '2022-01-09 19:00:00', '2022-01-09 21:45:00', 5, 1, 3, 4, 'REGULATION', NULL, 30, 25, 10, 12, 1, 1, 0, 0, 0, 0, 0, 'Arena 5', 17000),
(2, '2022-01-10 19:30:00', '2022-01-10 22:00:00', 3, 4, 2, 3, 'OVERTIME', NULL, 35, 30, 8, 10, 1, 2, 0, 0, 1, 0, 1, 'Arena 3', 16500),

(3, '2021-01-01 19:00:00', '2021-01-01 21:45:00', 1, 2, 3, 4, 'REGULATION', NULL, 30, 35, 10, 8, 1, 2, 0, 0, 0, 0, 0, 'Arena 1', 18000),
(3, '2021-01-02 19:30:00', '2021-01-02 22:15:00', 3, 4, 2, 3, 'REGULATION', NULL, 25, 30, 12, 10, 1, 1, 0, 0, 0, 0, 0, 'Arena 3', 16200),
(3, '2021-01-03 19:00:00', '2021-01-03 22:00:00', 2, 5, 4, 2, 'REGULATION', NULL, 35, 30, 8, 12, 2, 1, 0, 0, 0, 0, 0, 'Arena 2', 17500),
(3, '2021-01-04 19:30:00', '2021-01-04 22:00:00', 4, 1, 5, 3, 'REGULATION', NULL, 30, 25, 10, 10, 2, 1, 0, 0, 0, 0, 0, 'Arena 4', 15500),
(3, '2021-01-05 19:00:00', '2021-01-05 21:45:00', 5, 3, 2, 4, 'REGULATION', NULL, 35, 30, 12, 8, 1, 2, 0, 0, 0, 0, 0, 'Arena 5', 16800),
(3, '2021-01-06 19:30:00', '2021-01-06 22:15:00', 1, 4, 3, 2, 'REGULATION', NULL, 30, 25, 10, 12, 1, 1, 0, 0, 0, 0, 0, 'Arena 1', 17800),
(3, '2021-01-07 19:00:00', '2021-01-07 21:45:00', 2, 3, 4, 3, 'OVERTIME', NULL, 35, 30, 8, 10, 2, 1, 0, 0, 1, 0, 1, 'Arena 2', 17000),
(3, '2021-01-08 19:30:00', '2021-01-08 22:15:00', 4, 5, 2, 5, 'REGULATION', NULL, 30, 35, 10, 12, 1, 2, 0, 0, 0, 0, 0, 'Arena 4', 16000),
(3, '2021-01-09 19:00:00', '2021-01-09 21:45:00', 3, 1, 3, 2, 'REGULATION', NULL, 25, 30, 12, 10, 1, 1, 0, 0, 0, 0, 0, 'Arena 3', 16500),
(3, '2021-01-10 19:30:00', '2021-01-10 22:15:00', 5, 2, 4, 3, 'REGULATION', NULL, 35, 30, 8, 12, 2, 1, 0, 0, 0, 0, 0, 'Arena 5', 17200);


INSERT INTO player_game (player_id, game_id, time_on_ice, goals, assists, shots, hits, blocks, saves, goals_allowed) VALUES 
(1, 1, '18:23', 2, 1, 6, 3, 1, NULL, NULL),
(2, 1, '21:15', 1, 2, 5, 2, 2, NULL, NULL),
(3, 1, '19:40', 0, 1, 4, 4, 1, NULL, NULL),
(4, 1, '17:30', 1, 0, 5, 1, 0, NULL, NULL),
(5, 1, '15:45', 0, 0, 3, 2, 0, NULL, NULL),
(6, 1, '19:20', 0, 1, 2, 3, 1, NULL, NULL),
(7, 1, '14:25', 1, 0, 4, 2, 0, NULL, NULL),
(8, 1, '16:30', 0, 0, 2, 1, 1, NULL, NULL),
(9, 1, '13:45', 0, 0, 1, 0, 0, NULL, NULL),
(10, 1, '20:30', 0, 0, 0, 0, 0, 25, 2),
(11, 1, '21:40', 0, 0, 0, 0, 0, 28, 3),
(12, 1, '19:30', 0, 0, 0, 0, 0, 22, 2),
(13, 1, '00:00', NULL, NULL, NULL, NULL, NULL, 0, 0),
(14, 1, '00:00', NULL, NULL, NULL, NULL, NULL, 0, 0),

(1, 2, '19:15', 1, 2, 7, 4, 2, NULL, NULL),
(2, 2, '20:45', 1, 1, 6, 3, 1, NULL, NULL),
(3, 2, '18:30', 0, 1, 4, 3, 0, NULL, NULL),
(4, 2, '16:20', 1, 0, 4, 2, 0, NULL, NULL),
(5, 2, '14:15', 0, 1, 3, 1, 0, NULL, NULL),
(6, 2, '18:45', 1, 0, 3, 2, 1, NULL, NULL),
(7, 2, '15:30', 0, 1, 2, 2, 0, NULL, NULL),
(8, 2, '13:45', 0, 0, 1, 1, 0, NULL, NULL),
(9, 2, '12:30', 0, 0, 0, 0, 0, NULL, NULL),
(10, 2, '20:15', 0, 0, 0, 0, 0, 23, 3),
(11, 2, '21:30', 0, 0, 0, 0, 0, 29, 2),
(12, 2, '19:00', 0, 0, 0, 0, 0, 20, 2),
(13, 2, '00:00', NULL, NULL, NULL, NULL, NULL, 0, 0),
(14, 2, '00:00', NULL, NULL, NULL, NULL, NULL, 0, 0),

(1, 3, '18:45', 2, 0, 5, 3, 1, NULL, NULL),
(2, 3, '20:30', 1, 1, 4, 2, 1, NULL, NULL),
(3, 3, '19:20', 0, 1, 3, 2, 0, NULL, NULL),
(4, 3, '17:15', 1, 0, 4, 1, 0, NULL, NULL),
(5, 3, '15:30', 0, 1, 2, 1, 0, NULL, NULL),
(6, 3, '18:15', 0, 0, 2, 2, 1, NULL, NULL),
(7, 3, '14:05', 1, 0, 3, 1, 0, NULL, NULL),
(8, 3, '13:20', 0, 0, 1, 0, 0, NULL, NULL),
(9, 3, '12:15', 0, 0, 0, 0, 0, NULL, NULL),
(10, 3, '20:00', 0, 0, 0, 0, 0, 26, 2),
(11, 3, '21:15', 0, 0, 0, 0, 0, 30, 3),
(12, 3, '19:00', 0, 0, 0, 0, 0, 21, 2),
(13, 3, '00:00', NULL, NULL, NULL, NULL, NULL, 0, 0),
(14, 3, '00:00', NULL, NULL, NULL, NULL, NULL, 0, 0);

-- WITH PlayerImpact AS (
--     SELECT
--         ps.player_id,
--         s.year AS SeasonYear,
--         (SUM(ps.goals) + SUM(ps.assists) + SUM(ps.points)) AS TotalImpact,
--         SUM(ps.time_on_ice) AS TotalTimeOnIce,
--         (SUM(ps.goals) + SUM(ps.assists) + SUM(ps.points)) * 1.0 / SUM(ps.time_on_ice) AS ImpactRatio
--     FROM player_season_stats ps
--     JOIN season s ON ps.season_id = s.season_id
--     WHERE s.year = '2022-2023'
--     GROUP BY ps.player_id
-- )
-- SELECT
--     p.first_name || ' ' || p.last_name AS PlayerName,
--     pi.SeasonYear,
--     pi.TotalImpact,
--     pi.TotalTimeOnIce,
--     pi.ImpactRatio
-- FROM PlayerImpact pi
-- JOIN player p ON pi.player_id = p.player_id
-- ORDER BY pi.ImpactRatio DESC
-- LIMIT 1;

SELECT
    p.first_name || ' ' || p.last_name AS player_name,
    g.season_id,
    COUNT(pg.game_id) AS impactful_games,
    SUM(pg.saves) AS total_saves
FROM player_game pg
JOIN player p ON pg.player_id = p.player_id
JOIN game g ON pg.game_id = g.game_id
GROUP BY p.player_id, g.season_id
HAVING impactful_games > 2 AND (total_saves > 50)
ORDER BY total_saves DESC;
Fork
Embed on website
