Label game performance by margin
Company: Atlassian
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given a games DataFrame (or R data.frame) with columns: team_id, opponent_team_id, team_score, opponent_team_score, write code to: (1) Define a function that maps margin = team_score - opponent_team_score to labels: 'Blowout Win' (margin >= 20), 'Close Win' (1–19), 'Tie' (0), 'Close Loss' (-1 to -19), 'Blowout Loss' (<= -20). (2) Apply it to create a new column performance_label for every row. Provide both: (a) a vectorized Python solution using pandas (no per-row Python loop), and (b) an idiomatic R solution using dplyr or base R. Validate with this tiny example: team_id,opponent_team_id,team_score,opponent_team_score -> (1,2,95,70) => Blowout Win; (1,3,82,85) => Close Loss; (2,1,93,93) => Tie.
Overview: This question evaluates proficiency in data manipulation and transformation by mapping numeric score margins to categorical labels and implementing vectorized operations in pandas as well as idiomatic R data-frame workflows.
You are given a table games that records basketball game results with each row representing one game from the perspective of a particular team. Using SQL, write a query that returns all existing columns and adds a derived column performance_label based on the score margin defined as (team_score - opponent_team_score), using the following rules:
- 'Blowout Win' if margin >= 20
- 'Close Win' if margin BETWEEN 1 AND 19
- 'Tie' if margin = 0
- 'Close Loss' if margin BETWEEN -19 AND -1
- 'Blowout Loss' if margin <= -20
Return one row per game with the new performance_label column computed according to these rules.
Tables
games(game_id INT, team_id INT, opponent_team_id INT, team_score INT, opponent_team_score INT)
Hints
- Compute the score margin using team_score - opponent_team_score.
- Use a CASE expression to map the margin ranges to the required labels.