Atlassian·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

SQL heavy technical screen for a Data Scientist role at Atlassian. One question, but it had a lot of layers: bidirectional game data, window functions, deterministic tie-breaking. The kind of problem where you think you have it and then realize you forgot to union the opponent's perspective.

Questions Asked (1)

Given a games table where each row represents one team's perspective in a matchup, write a SQL query that treats every game from both teams' viewpoints, then returns each team's top 3 highest-scoring games for the 2024 season. Include team name, opponent name, team score, and a rank column. Break ties by score descending, then date descending, then game_id ascending.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures

Author's notes

The union step is where I almost tripped up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, normalize the games table by creating a union of two SELECT statements: one for each team's perspective (team as home, opponent as away) and one for the reverse. Then, use a window function like ROW_NUMBER() or RANK() partitioned by team and ordered by score DESC, date DESC, game_id ASC to assign ranks, and finally filter to the top 3 per team for the 2024 season.

Pro tip: When using window functions, be explicit about the tie-breaking order and consider whether RANK() or ROW_NUMBER() is more appropriate; ROW_NUMBER() ensures exactly 3 rows per team even with ties, while RANK() may return more than 3 if ties occur at the boundary.

1. Understand the table structure and requirements

Identify the columns in the games table (e.g., game_id, date, home_team, away_team, home_score, away_score) and clarify that each row represents one team's perspective. The goal is to transform it so each game appears twice, once for each team.

2. Normalize the data with UNION ALL

Write two SELECT statements: one selecting home_team as team, away_team as opponent, home_score as team_score, and another selecting away_team as team, home_team as opponent, away_score as team_score. Combine them with UNION ALL to preserve all rows.

3. Filter for the 2024 season

Apply a WHERE clause to restrict the data to games played in the 2024 season, using the date column (e.g., EXTRACT(YEAR FROM date) = 2024 or date BETWEEN '2024-01-01' AND '2024-12-31').

4. Rank each team's games using window functions

Use ROW_NUMBER() OVER (PARTITION BY team ORDER BY team_score DESC, date DESC, game_id ASC) to assign a rank to each game per team. This ensures deterministic ordering and handles ties as specified.

5. Select top 3 per team and output required columns

Wrap the ranked query in a subquery or CTE, then filter WHERE rank <= 3. Finally, select team, opponent, team_score, and rank for the output.

Key Points to Mention

The need to normalize the table using UNION ALL to get both teams' perspectives.
Use of window functions (ROW_NUMBER, RANK, or DENSE_RANK) with PARTITION BY team and ORDER BY score DESC, date DESC, game_id ASC.
Filtering for the 2024 season before or after ranking (preferably before to reduce data size).
Handling ties: ROW_NUMBER ensures exactly 3 rows per team, while RANK may return more if ties at the boundary.
The importance of deterministic ordering by including date and game_id in the ORDER BY clause.
Potential performance considerations: filtering early and using appropriate indexes.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.