Katkat

Find 2023 NCAA championship winner

AtlassianData Scientist · Technical Screen

Last updated

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. 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.