Employees Project Allocation - Problem

You are given two tables: Project and Employees.

The Project table contains information about project assignments with columns:

  • project_id (int): The project identifier
  • employee_id (int): The employee identifier (primary key, foreign key to Employee table)
  • workload (int): The workload of the project for this employee

The Employees table contains employee information with columns:

  • employee_id (int): The employee identifier (primary key)
  • name (varchar): The employee name
  • team (varchar): The team the employee belongs to

Task: Find the employees who are allocated to projects with a workload that exceeds the average workload of all employees for their respective teams.

Return the result table ordered by employee_id, project_id in ascending order.

Table Schema

Project
Column Name Type Description
project_id int Project identifier
employee_id PK int Employee identifier (primary key, foreign key to Employees)
workload int Workload hours for this project assignment
Primary Key: employee_id
Employees
Column Name Type Description
employee_id PK int Employee identifier (primary key)
name varchar Employee full name
team varchar Team name the employee belongs to
Primary Key: employee_id

Input & Output

Example 1 — Basic Team Comparison
Input Tables:
Project
project_id employee_id workload
1 10 40
2 20 25
3 30 15
4 40 30
Employees
employee_id name team
10 Alice A
20 Bob A
30 Charlie B
40 David B
Output:
project_id employee_id name team workload
1 10 Alice A 40
4 40 David B 30
💡 Note:

Team A average workload: (40 + 25) / 2 = 32.5. Alice's workload (40) exceeds this average.

Team B average workload: (15 + 30) / 2 = 22.5. David's workload (30) exceeds this average, but Charlie's (15) does not.
Example 2 — Single Team Member
Input Tables:
Project
project_id employee_id workload
1 10 50
Employees
employee_id name team
10 Alice A
Output:
project_id employee_id name team workload
💡 Note:

Alice is the only member of team A, so her workload (50) equals the team average (50). Since she doesn't exceed the average, no rows are returned.

Constraints

  • 1 ≤ project_id ≤ 1000
  • 1 ≤ employee_id ≤ 1000
  • 1 ≤ workload ≤ 100
  • team names are non-empty strings
  • Each employee belongs to exactly one team

Visualization

Tap to expand
Employees Project Allocation INPUT DATA Employees Table emp_id name team 1 Alice Dev 2 Bob Dev 3 Carol QA 4 Dave QA Project Table proj_id emp_id workload P1 1 50 P2 1 30 P1 2 20 P1 3 60 P2 4 25 JOIN on employee_id Find Above Avg ALGORITHM STEPS 1 Join Tables Combine Project + Employees ON p.emp_id = e.emp_id 2 Calc Team Averages GROUP BY team AVG(workload) per team Dev avg: (50+30+20)/3 = 33.3 QA avg: (60+25)/2 = 42.5 3 Compare Workloads Filter: workload > team_avg Alice P1: 50 > 33.3 [OK] Alice P2: 30 < 33.3 [NO] Bob P1: 20 < 33.3 [NO] Carol P1: 60 > 42.5 [OK] 4 Order Results BY emp_id, proj_id FINAL RESULT Employees Above Team Average emp_id name proj_id 1 Alice P1 3 Carol P1 Workload Comparison Dev Team (avg: 33) Alice: 50 Bob: 20 QA Team (avg: 42) Carol: 60 Dave: 25 Above avg Below avg Key Insight: Use a subquery or CTE to calculate team averages first, then join back to filter individual project allocations. Window functions (AVG() OVER PARTITION BY team) provide an elegant solution by computing averages alongside each row without explicit grouping. TutorialsPoint - Employees Project Allocation | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18
23.4K Views
Medium Frequency
~18 min Avg. Time
845 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen