LeetCode 1077: Project Employees III

Problem Restatement

Using Project(project_id, employee_id) and Employee(employee_id, name, experience_years), find the most experienced employee for each project.

If there are ties, return all tied 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 p.project_id, p.employee_id
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
WHERE (p.project_id, e.experience_years) IN (
    SELECT p2.project_id, MAX(e2.experience_years)
    FROM Project p2
    JOIN Employee e2 ON p2.employee_id = e2.employee_id
    GROUP BY p2.project_id
);

Alternative with Window Function

WITH ranked AS (
    SELECT p.project_id, p.employee_id,
           RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) AS rk
    FROM Project p
    JOIN Employee e ON p.employee_id = e.employee_id
)
SELECT project_id, employee_id
FROM ranked
WHERE rk = 1;

Code Explanation

The subquery finds the maximum experience years per project.

The outer query selects employees whose (project_id, experience_years) pair matches the maximum for their project.

The window function approach ranks employees within each project by experience descending and selects rank 1 (handles ties automatically).