Atlassian·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

SQL question for a Data Scientist role at Atlassian. One question, reasonably tricky, the kind where you think you've got it and then realize there's a wrinkle you almost missed.

Questions Asked (1)

Given a team table and a game table, write a single SQL query to find the team name of the 2023 championship winner. The winner could be stored as either team_id or opponent_team_id, there are no ties assumed, but if one exists in the data you should break it by higher score. No hard-coded IDs allowed.

Data ModelingProduct Analytics & Metrics

Author's notes

The part that almost got me was the bidirectional storage.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify the championship game by filtering the game table for the 2023 season and selecting the row with the highest score (or the single game if no ties). Then, determine the winning team by comparing the scores of the two teams involved, and join to the team table to retrieve the team name. Use a CTE or subquery to isolate the final game and handle the winner logic without hard-coded IDs.

Pro tip: Explicitly mention that you're handling potential ties by ordering by score descending and limiting to 1, and that you're avoiding hard-coded IDs by using the season year and score comparisons. This shows attention to data quality and robustness.

1. Filter and isolate the championship game

Filter the game table for the 2023 season and order by score descending to get the highest-scoring game. Use LIMIT 1 to handle potential ties.

2. Determine the winning team ID

In the isolated game row, compare the scores of team_id and opponent_team_id. Use a CASE expression to select the team_id with the higher score as the winner_id.

3. Join to team table for the name

Join the winner_id to the team table on team_id to retrieve the corresponding team name.

4. Write the final query

Combine the steps into a single SQL query using a CTE or subquery for clarity, ensuring no hard-coded IDs are used.

Key Points to Mention

Use of CTE or subquery to isolate the championship game
Handling ties by ordering by score descending and limiting to 1
Using CASE expression to determine winner based on scores
Joining to team table to get team name
Avoiding hard-coded IDs by using season year and score comparisons
Assumption that there is exactly one championship game per season

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