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 - SQL Solution INPUT TABLES Project Table project_id employee_id 1 1 1 2 1 3 2 1 Employee Table emp_id name exp_years 1 Alice 3 2 Bob 2 3 Carol 1 Project 1: 3 employees Project 2: 1 employee ALGORITHM STEPS 1 Count Employees GROUP BY project_id COUNT(employee_id) 2 Find Maximum Get max employee count MAX(emp_count) 3 Filter Projects HAVING count = max WHERE cnt = max_cnt 4 Return Result SELECT project_id SELECT project_id FROM Project GROUP BY project_id HAVING COUNT(*) = (SELECT MAX(cnt)...) FINAL RESULT Projects with most employees: Project 1 3 employees Project 2 1 employee MAXIMUM Output: project_id 1 OK - Project 1 has max (3) Project 1 employees: Alice, Bob, Carol Key Insight: Use a subquery with MAX() on grouped counts to find the maximum number of employees. Then filter with HAVING to return ALL projects matching that maximum count, handling ties where multiple projects share the highest employee count. TutorialsPoint - Project Employees II | Optimal Solution

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