Find 2023 NCAA championship winner

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Atlassian.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Atlassian during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Find 2023 NCAA championship winner