Calculate User Deviation from Team Average Messages
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
usage_stats
+---------+---------+---------------+------------+
| user_id | team_id | messages_sent | date |
+---------+---------+---------------+------------+
| 1 | 10 | 8 | 2024-05-01 |
| 2 | 10 | 3 | 2024-05-01 |
| 3 | 20 | 15 | 2024-05-02 |
| 4 | 20 | 9 | 2024-05-02 |
| 5 | 30 | 0 | 2024-05-03 |
+---------+---------+---------------+------------+
##### Scenario
Analyst needs each user’s deviation from their team’s average sent messages without renaming columns in pandas.
##### Question
Write Python code that returns a DataFrame with an extra column ‘delta_from_team_mean’ using transform, and explain why transform works better than groupby.mean here.
##### Hints
transform broadcasts team means to original index; avoids column aggregation and renaming.
Overview: This question evaluates proficiency with pandas group-wise computations, broadcasting of group aggregates, and DataFrame manipulation without renaming columns.
Given the usage_stats table, write an SQL query that returns all original columns plus a new column delta_from_team_mean. For each row, delta_from_team_mean should be messages_sent minus the average messages_sent for that row’s team_id, computed over all users in the same team.
Tables
usage_stats(user_id INTEGER, team_id INTEGER, messages_sent INTEGER, date DATE)
Hints
- Use a window function to compute the team average without collapsing rows.
- Compute AVG(messages_sent) OVER (PARTITION BY team_id) and subtract it from messages_sent for each row.