Project Employees II - Problem
Project Employees II is a SQL database problem that challenges you to find projects with the maximum number of employees. You're given two related tables:
The Project table contains project-employee assignments with columns
Your task is to identify which projects have the highest employee count. If multiple projects tie for the maximum number of employees, return all of them. This problem tests your ability to work with aggregate functions, subqueries, and GROUP BY clauses in SQL.
Project and Employee.The Project table contains project-employee assignments with columns
project_id and employee_id. The Employee table contains employee details including employee_id, name, and experience_years.Your task is to identify which projects have the highest employee count. If multiple projects tie for the maximum number of employees, return all of them. This problem tests your ability to work with aggregate functions, subqueries, and GROUP BY clauses in SQL.
Input & Output
example_1.sql — Basic Case
$
Input:
Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| 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 > 2, Project 1 has the most employees.
example_2.sql — Tie Case
$
Input:
Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Alice | 5 |
| 2 | Bob | 3 |
| 3 | Carol | 4 |
| 4 | Dave | 2 |
| 5 | Eve | 1 |
+-------------+--------+------------------+
›
Output:
+-------------+
| project_id |
+-------------+
| 1 |
| 2 |
+-------------+
💡 Note:
Both Project 1 and Project 2 have 2 employees each, which is the maximum. Project 3 has only 1 employee. Return both projects with maximum employees.
example_3.sql — Single Project
$
Input:
Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | John | 5 |
+-------------+--------+------------------+
›
Output:
+-------------+
| project_id |
+-------------+
| 1 |
+-------------+
💡 Note:
Only one project exists with one employee. It has the maximum (and only) employee count, so return Project 1.
Constraints
- 1 ≤ Project.project_id ≤ 103
- 1 ≤ Project.employee_id ≤ 104
- 1 ≤ Employee.employee_id ≤ 104
- 1 ≤ Employee.name.length ≤ 20
- 1 ≤ Employee.experience_years ≤ 20
- All employee_id values are unique in Employee table
- Each (project_id, employee_id) pair is unique in Project table
Visualization
Tap to expand
Understanding the Visualization
1
Count Team Members
For each project, count how many employees are assigned
2
Find Maximum Size
Determine what the largest team size is across all projects
3
Identify Top Projects
Return all projects that have the maximum team size
Key Takeaway
🎯 Key Insight: Use window functions like MAX() OVER() to calculate global statistics while grouping data, enabling efficient single-pass solutions for finding records with maximum/minimum values.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code