Team Workload Outlier Project Assignments

Hard

A workforce planning dashboard needs to flag project assignments where an employee's workload is heavier than the typical workload in that employee's team. Table: Project project_id: project identifier employee_id: employee assigned to this project row workload: workload units for this assignment Table: Employees employee_id: unique employee identifier name: employee display name team: team label Task: Find all joined Project-Employees rows where the row's workload is strictly greater than the average workload of all joined rows in the same team. Formal rule: Let avg_team_workload(team) be the arithmetic mean of workload over joined rows in that team. Keep a row if workload > avg_team_workload(team). Output requirements: Return exactly these columns in order: employee_id, project_id, employee_name, project_workload Sort final output by employee_id ASC, project_id ASC Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Project:
| project_id | employee_id | workload |
|------------|-------------|----------|
| 1          | 1           | 45       |
| 1          | 2           | 90       |
| 2          | 3           | 12       |
| 2          | 4           | 68       |

Employees:
| employee_id | name | team |
|-------------|------|------|
| 1           | Lena | A    |
| 2           | Omar | B    |
| 3           | Iris | B    |
| 4           | Noah | A    |

Output

[
  {"employee_id":2,"project_id":1,"employee_name":"Omar","project_workload":90},
  {"employee_id":4,"project_id":2,"employee_name":"Noah","project_workload":68}
]

Explanation

Team A average is 56.5, so only workload 68 qualifies. Team B average is 51, so only workload 90 qualifies.

Example 2

Input

Project:
| project_id | employee_id | workload |
|------------|-------------|----------|
| 10         | 100         | 40       |
| 10         | 101         | 60       |
| 11         | 102         | 50       |
| 11         | 103         | 50       |

Employees:
| employee_id | name | team |
|-------------|------|------|
| 100         | Aria | Blue |
| 101         | Bo   | Blue |
| 102         | Cy   | Gold |
| 103         | Dia  | Gold |

Output

[
  {"employee_id":101,"project_id":10,"employee_name":"Bo","project_workload":60}
]

Explanation

Blue team average is 50 so only employee 101 qualifies. Gold team average is 50 so neither row qualifies.

Example 3

Input

Project:
| project_id | employee_id | workload |
|------------|-------------|----------|
| 20         | 200         | 75       |
| 20         | 201         | 25       |
| 21         | 202         | 25       |
| 22         | 999         | 90       |

Employees:
| employee_id | name | team |
|-------------|------|------|
| 200         | Eli  | Ops  |
| 201         | Fay  | Ops  |
| 202         | Gia  | Eng  |

Output

[
  {"employee_id":200,"project_id":20,"employee_name":"Eli","project_workload":75}
]

Explanation

The row for employee 999 is ignored by the inner join. Ops average is 50 so only 75 qualifies; Eng has one row equal to its average.

Constraints

1 <= number of rows in Project <= 200000
1 <= number of rows in Employees <= 100000
Project.employee_id values are unique per test case
Employees.employee_id values are unique per test case
1 <= project_id <= 2000000000
1 <= employee_id <= 2000000000
0 <= workload <= 1000000
name and team are non-empty strings
Output columns must be exactly: employee_id, project_id, employee_name, project_workload
Rows must be sorted by employee_id ASC, project_id ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [employee_id, project_id, employee_name, project_workload]

Project: | project_id | employee_id | workload | |------------|-------------|----------| | 1 | 1 | 45 | | 1 | 2 | 90 | | 2 | 3 | 12 | | 2 | 4 | 68 | Employees: | employee_id | name | team | |-------------|------|------| | 1 | Lena | A | | 2 | Omar | B | | 3 | Iris | B | | 4 | Noah | A |

Project: | project_id | employee_id | workload | |------------|-------------|----------| | 10 | 100 | 40 | | 10 | 101 | 60 | | 11 | 102 | 50 | | 11 | 103 | 50 | Employees: | employee_id | name | team | |-------------|------|------| | 100 | Aria | Blue | | 101 | Bo | Blue | | 102 | Cy | Gold | | 103 | Dia | Gold |

Project: | project_id | employee_id | workload | |------------|-------------|----------| | 20 | 200 | 75 | | 20 | 201 | 25 | | 21 | 202 | 25 | | 22 | 999 | 90 | Employees: | employee_id | name | team | |-------------|------|------| | 200 | Eli | Ops | | 201 | Fay | Ops | | 202 | Gia | Eng |