Project Employees II - Solution & Explanation
Problem Statement
Table: Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) is the primary key (combination of columns with unique values) of this table.
employee_id is a foreign key (reference column) to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.
Table: Employee
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id is the primary key (column with unique values) of this table. Each row of this table contains information about one employee.
Write a solution to report all the projects that have the most employees.
Return the result table in any order.
The result format is in the following example.
Example 1:
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 | +-------------+ Explanation: The first project has 3 employees while the second one has 2.
Approach Overview
Problem Overview: The table Project(project_id, employee_id) stores which employees are assigned to each project. The task is to return the project_id that has the highest number of employees assigned. If multiple projects share the same maximum employee count, all of them should be returned.
Approach 1: GROUP BY with ORDER BY and LIMIT (O(n) time, O(k) space)
The most straightforward way is to aggregate employee counts per project using GROUP BY project_id. Once grouped, apply COUNT(employee_id) to compute the number of employees in each project. Then sort the results using ORDER BY COUNT(employee_id) DESC and select the top result with LIMIT 1. The query scans all rows once to build the grouped counts, which takes O(n) time where n is the number of rows in the table. The database stores aggregated groups internally, requiring O(k) space where k is the number of unique projects. This approach works well when you only need one project with the highest count, but it does not correctly return multiple projects if there is a tie.
Approach 2: GROUP BY with MAX Subquery (O(n) time, O(k) space)
A more robust approach calculates employee counts per project and then filters projects whose counts match the global maximum. First compute COUNT(employee_id) grouped by project_id. Then use a subquery to determine the maximum employee count among those groups. Finally, use HAVING to return only the projects whose counts equal that maximum value. The database still performs a full scan of the table and aggregates rows, so the time complexity remains O(n). Space complexity is O(k) for storing grouped counts. This method correctly handles ties because every project matching the maximum count is returned.
Both approaches rely on SQL aggregation concepts such as GROUP BY, COUNT, and filtering grouped results using HAVING. Understanding how relational databases execute aggregation queries is key when solving SQL and database interview problems. Many similar problems use the same pattern: compute grouped statistics first, then filter using a subquery or window function.
Recommended for interviews: The GROUP BY with MAX subquery approach is the safer and more correct solution because it handles ties without relying on ordering tricks. Interviewers typically expect you to recognize the aggregation pattern, compute per-group counts, and compare them against the global maximum using HAVING. The ORDER BY ... LIMIT approach demonstrates understanding of aggregation but may fail when multiple projects share the same highest employee count.
Solution
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| GROUP BY with ORDER BY LIMIT | O(n) | O(k) | Quick solution when only one maximum result is required and ties are not a concern |
| GROUP BY with MAX Subquery | O(n) | O(k) | Best general solution when multiple projects may share the highest employee count |
Video Solution
LeetCode 1076 "Project Employees II" Meta Interview SQL Question with Explanation • Everyday Data Science • 2,101 views views
Watch 4 more video solutions →Frequently Asked Questions
Is Project Employees II easy or hard?
Project Employees II Python/Java solution
How to solve Project Employees II in O(n)?
What is the best approach for Project Employees II?
Is Project Employees II asked at Google/Amazon/Meta?
What data structure is used in Project Employees II?
What is the time complexity of Project Employees II?
Ready to solve this problem?
Practice Project Employees II with our built-in code editor and test cases.
Practice on FleetCode