Rank each team’s top 3 scores in 2024
Company: Atlassian
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using the same schema, compute each team’s top 3 highest-scoring games for season_year = 2024, treating each game from BOTH teams’ perspectives (i.e., if A vs B is one row, also consider B’s score in that game). Return columns: team_name, opponent_team_name, team_score, score_rank (1 = highest per team). Break ties deterministically by: team_score DESC, date DESC, game_id ASC. 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 |
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+
| 1101 | 2024-01-15 | 2024 | 3 | 4 | 88 | 79 | REGULAR |
| 1102 | 2024-02-01 | 2024 | 1 | 3 | 95 | 70 | REGULAR |
| 1103 | 2024-02-20 | 2024 | 1 | 4 | 82 | 85 | REGULAR |
| 1104 | 2024-03-03 | 2024 | 2 | 1 | 91 | 93 | REGULAR |
| 1105 | 2024-03-10 | 2024 | 2 | 3 | 77 | 74 | REGULAR |
+---------+------------+-------------+---------+-------------------+------------+---------------------+---------------+
Write a single SQL query (CTEs allowed) that produces the required columns with row_number-based ranking.
Overview: This question evaluates data manipulation and relational-query competencies, focusing on per-entity ranking, deterministic tie-breaking, and reshaping game-level records into team-centric perspectives.
Using the tables below, compute each team’s top 3 highest-scoring games for season_year = 2024, treating each game from both teams’ perspectives (i.e., if A vs B is one row, also consider B’s score in that game). Return columns: team_name, opponent_team_name, team_score, score_rank (1 = highest per team). Break ties deterministically by: team_score DESC, date DESC, game_id ASC. Write a single SQL query (CTEs allowed) that uses row_number-based ranking to produce the required result.
Tables
team(team_id INT, team_name VARCHAR(100))
game(game_id INT, team_id INT, opponent_team_id INT, date DATE, season_year INT, team_score INT, opponent_team_score INT, game_type VARCHAR(20))
Hints
- Think about transforming each game into two rows: one for each team’s perspective.
- Use ROW_NUMBER() partitioned by team_id with the specified ORDER BY to rank scores per team, then filter to the top 3.