Team Workload Outlier Project Assignments

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Project =

[{"project_id":1,"employee_id":1,"workload":45},{"project_id":1,"employee_id":2,"workload":90},{"project_id":2,"employee_id":3,"workload":12},{"project_id":2,"employee_id":4,"workload":68}]

Employees =

[{"employee_id":1,"name":"Lena","team":"A"},{"employee_id":2,"name":"Omar","team":"B"},{"employee_id":3,"name":"Iris","team":"B"},{"employee_id":4,"name":"Noah","team":"A"}]

Team Workload Outlier Project Assignments

Hints

Team Workload Outlier Project Assignments

Hints