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 OverviewInput TablesProjectAlice: 40hrsBob: 25hrsTeam workloadsEmployeesAlice → Team ABob → Team ATeam assignmentsJOIN & COMPARETeam Avg: 32.5OutputAbove Average EmployeesAlice: 40 > 32.5 ✓Exceeds team averageAlgorithm Steps1. JOIN tables by employee_id2. Calculate team average workload3. Filter: workload > team_avg
Understanding the Visualization
1
Join Tables
Combine Project and Employees data
2
Calculate Average
Find team average workload
3
Filter Results
Select employees above team average
Key Takeaway
🎯 Key Insight: Use subqueries or window functions to compare individual values against group aggregates

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