Find 2023 NCAA championship winner
Company: Atlassian
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
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.
Quick Answer: This question evaluates proficiency in SQL data manipulation and relational reasoning, testing the ability to identify a championship winner from game result records across related tables in the Data Manipulation (SQL/Python) domain for data scientist roles.