Find 2023 NCAA championship winner

You are given two tables. 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 |
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+
| 1001 | 2023-04-03 | 2023 | 1 | 2 | 76 | 59 | CHAMPIONSHIP |
| 1101 | 2024-01-15 | 2024 | 3 | 4 | 88 | 79 | REGULAR |
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+

Write a single SQL query to return the team_name of the 2023 CHAMPIONSHIP winner. Requirements: (1) Determine the winner even if the champion is stored either as team_id or opponent_team_id. (2) Assume no ties in CHAMPIONSHIP; if a tie exists due to data issues, break by higher score. (3) Use only the two tables and no hard-coded team ids.

Find the 2023 NCAA Championship Winner

You are given two tables:

team(team_id INT PRIMARY KEY, team_name VARCHAR(100))

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 VARCHAR(50))

Write a single SQL query to return the team_name of the 2023 CHAMPIONSHIP winner.

Requirements:

1. Correctly determine the winner even if the champion is stored either as team_id or opponent_team_id.
2. Assume no ties in CHAMPIONSHIP games; if a tie exists due to data issues, break it by picking the team with the higher score.
3. Use only the two tables and do not hard-code any specific team ids.

Use the schema and sample data below.

Table team

column	type	constraints
team_id	INT	PRIMARY KEY, NOT NULL
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, NOT NULL
date	DATE	NOT NULL
season_year	INT	NOT NULL
team_id	INT	NOT NULL
opponent_team_id	INT	NOT NULL
team_score	INT	NOT NULL
opponent_team_score	INT	NOT NULL
game_type	VARCHAR(50)	NOT NULL

Sample data:

date	game_id	game_type	opponent_team_id	opponent_team_score	season_year	team_id	team_score
2023-04-03	1001	CHAMPIONSHIP	2	59	2023	1	76
2024-01-15	1101	REGULAR	4	79	2024	3	88

Expected output

Return a single row containing the team_name of the 2023 CHAMPIONSHIP winner.

team_name
UConn Huskies

Hints

1. Treat team_id and opponent_team_id as separate rows with their respective scores (UNION ALL).
2. Once each team-score pair is on its own row, filter to 2023 CHAMPIONSHIP games and pick the highest score.