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.
Overview: 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.
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.
Tables
team(team_id INT, team_name VARCHAR(100))
game(game_id INT, date DATE, season_year INT, team_id INT, opponent_team_id INT, team_score INT, opponent_team_score INT, game_type VARCHAR(50))
Hints
- Treat team_id and opponent_team_id as separate rows with their respective scores (UNION ALL).
- Once each team-score pair is on its own row, filter to 2023 CHAMPIONSHIP games and pick the highest score.