Rank each team’s top 3 scores in 2024

Using the same schema, compute each team’s top 3 highest-scoring games for season_year = 2024, treating each game from BOTH teams’ perspectives (i.e., if A vs B is one row, also consider B’s score in that game). Return columns: team_name, opponent_team_name, team_score, score_rank (1 = highest per team). Break ties deterministically by: team_score DESC, date DESC, game_id ASC. Schema: team(team_id INT PRIMARY KEY, team_name TEXT); game(game_id INT PRIMARY KEY, team_id INT, opponent_team_id INT, date DATE, season_year INT, team_score INT, opponent_team_score INT, game_type TEXT). Sample data: team

+---------+------------------------------+
| team_id | team_name |
+---------+------------------------------+
| 1 | UConn Huskies |
| 2 | San Diego State Aztecs |
| 3 | Alabama Crimson Tide |
| 4 | Purdue Boilermakers |
+---------+------------------------------+

game
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+
| game_id | date | season_year | team_id | opponent_team_id | team_score | opponent_team_score | game_type |
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+
| 1101 | 2024-01-15 | 2024 | 3 | 4 | 88 | 79 | REGULAR |
| 1102 | 2024-02-01 | 2024 | 1 | 3 | 95 | 70 | REGULAR |
| 1103 | 2024-02-20 | 2024 | 1 | 4 | 82 | 85 | REGULAR |
| 1104 | 2024-03-03 | 2024 | 2 | 1 | 91 | 93 | REGULAR |
| 1105 | 2024-03-10 | 2024 | 2 | 3 | 77 | 74 | REGULAR |
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+

Write a single SQL query (CTEs allowed) that produces the required columns with row_number-based ranking.

Rank each team’s top 3 scores in 2024

Table team

column	type	constraints
team_id	INT	PRIMARY KEY
team_name	VARCHAR(100)	NOT NULL

Sample data:

team_id	team_name
1	UConn Huskies
2	San Diego State Aztecs
3	Alabama Crimson Tide
4	Purdue Boilermakers

Table game

column	type	constraints
game_id	INT	PRIMARY KEY
team_id	INT	NOT NULL
opponent_team_id	INT	NOT NULL
date	DATE	NOT NULL
season_year	INT	NOT NULL
team_score	INT	NOT NULL
opponent_team_score	INT	NOT NULL
game_type	VARCHAR(20)	NOT NULL

Sample data:

date	game_id	game_type	opponent_team_id	opponent_team_score	season_year	team_id	team_score
2024-01-15	1101	REGULAR	4	79	2024	3	88
2024-02-01	1102	REGULAR	3	70	2024	1	95
2024-02-20	1103	REGULAR	4	85	2024	1	82
2024-03-03	1104	REGULAR	1	93	2024	2	91
2024-03-10	1105	REGULAR	3	74	2024	2	77

Expected output

For each team, list up to their top 3 games (by points scored in 2024), considering both when they appear as team_id and as opponent_team_id. Each row includes the team’s name, their opponent’s name, the points that team scored in that game, and a score-based rank per team (1 = highest score). Ties are broken by team_score DESC, date DESC, game_id ASC.

opponent_team_name	score_rank	team_name	team_score
Purdue Boilermakers	1	Alabama Crimson Tide	88
San Diego State Aztecs	2	Alabama Crimson Tide	74
UConn Huskies	3	Alabama Crimson Tide	70
UConn Huskies	1	Purdue Boilermakers	85
Alabama Crimson Tide	2	Purdue Boilermakers	79
UConn Huskies	1	San Diego State Aztecs	91
Alabama Crimson Tide	2	San Diego State Aztecs	77
Alabama Crimson Tide	1	UConn Huskies	95
San Diego State Aztecs	2	UConn Huskies	93
Purdue Boilermakers	3	UConn Huskies	82

Hints

1. Think about transforming each game into two rows: one for each team’s perspective.
2. Use ROW_NUMBER() partitioned by team_id with the specified ORDER BY to rank scores per team, then filter to the top 3.