Project Employees II - Problem

You are given two tables: Project and Employee.

The Project table contains information about which employees are working on which projects, with a composite primary key of (project_id, employee_id).

The Employee table contains employee details including their experience years.

Task: Write a SQL query to find all projects that have the most employees. Return the result in any order.

Table Schema

Project
Column Name Type Description
project_id PK int Project identifier
employee_id PK int Employee identifier (foreign key to Employee table)
Primary Key: (project_id, employee_id)
Employee
Column Name Type Description
employee_id PK int Employee identifier (primary key)
name varchar Employee name
experience_years int Years of experience
Primary Key: employee_id

Input & Output

Example 1 — Multiple Projects with Different Team Sizes
Input Tables:
Project
project_id employee_id
1 1
1 2
1 3
2 1
2 4
Employee
employee_id name experience_years
1 Khaled 3
2 Ali 2
3 John 1
4 Doe 2
Output:
project_id
1
💡 Note:

Project 1 has 3 employees (1, 2, 3) while Project 2 has 2 employees (1, 4). Since 3 is the maximum number of employees, only Project 1 is returned.
Example 2 — Tie for Most Employees
Input Tables:
Project
project_id employee_id
1 1
1 2
2 3
2 4
Employee
employee_id name experience_years
1 Alice 3
2 Bob 2
3 Charlie 1
4 David 2
Output:
project_id
1
2
💡 Note:

Both Project 1 and Project 2 have exactly 2 employees each. Since both projects tie for the maximum number of employees, both are returned in the result.
Example 3 — Single Project
Input Tables:
Project
project_id employee_id
1 1
1 2
Employee
employee_id name experience_years
1 John 5
2 Jane 3
Output:
project_id
1
💡 Note:

Only one project exists with 2 employees, so it automatically has the most employees and is returned.

Constraints

  • 1 ≤ project_id ≤ 100
  • 1 ≤ employee_id ≤ 100
  • 1 ≤ experience_years ≤ 20
  • name consists of lowercase and uppercase English letters

Visualization

Tap to expand
Project Employees II: Find Projects with Most EmployeesProject Tableproject_idemployee_id1112132124Count per Projectproject_idcount1322GROUP BYMAX = 3Resultproject_id1Project 1 has 3 employees (maximum)Project 2 has 2 employees
Understanding the Visualization
1
Input
Project and Employee tables
2
Group & Count
Count employees per project
3
Filter Max
Return projects with maximum count
Key Takeaway
🎯 Key Insight: Use GROUP BY with COUNT and HAVING to find records with maximum aggregated values

Related Problems

Asked in
Amazon 15 Google 12 Microsoft 8
25.4K Views
Medium Frequency
~8 min Avg. Time
892 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