Skip to content

LeetCode 1076: Project Employees II

A clear explanation of finding the project with the most employees using GROUP BY, COUNT, and a subquery for the maximum.

leetcode database

Problem Restatement

Using the Project(project_id, employee_id) table, report the project(s) with the most employees.

Edge Cases

  • Match the aggregate to the question: use raw COUNT or SUM for rows/amounts, and COUNT(DISTINCT ...) only for unique entities.
  • Tie cases should return every qualifying row unless the statement asks for a single row.
  • For outer joins, keep filters on the joined table in the ON clause when unmatched rows must remain visible.

Common Pitfalls

  • Do not put aggregate filters in WHERE; use HAVING after GROUP BY.
  • When the answer needs all tied winners, avoid LIMIT 1 unless the statement explicitly asks for one row.

Implementation 

SELECT project_id
FROM Project
GROUP BY project_id
HAVING COUNT(employee_id) = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(employee_id) AS cnt
        FROM Project
        GROUP BY project_id
    ) AS sub
);

Code Explanation

The inner subquery counts employees per project.

The outer MAX(cnt) finds the maximum employee count.

The main query returns all projects whose employee count equals the maximum.

This handles ties (multiple projects with the same maximum count).