Project Employees III

Project Employees III

What is this problem about?

The Project Employees III SQL problem asks you to find the most experienced employee(s) for each project. If multiple employees have the same maximum experience in a project, return all of them. This medium SQL problem tests per-project ranking with ties handled correctly. The database interview pattern demonstrates window function usage.

Why is this asked in interviews?

Meta asks this to test the "find row with max value per group" pattern with tie handling. Using DENSE_RANK or a correlated subquery correctly returns multiple employees when they share the maximum experience.

Algorithmic pattern used

Window function with RANK or correlated subquery. Window: SELECT project_id, employee_id FROM (SELECT p.project_id, p.employee_id, RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) AS rnk FROM Project p JOIN Employee e ON p.employee_id = e.employee_id) t WHERE rnk = 1.

Example explanation

Project 1: emp1(5yrs), emp2(5yrs), emp3(3yrs). Project 2: emp4(7yrs). Max experience per project: proj1→5 (emp1 and emp2 tied), proj2→7 (emp4). Result: [(proj1,emp1),(proj1,emp2),(proj2,emp4)].

Common mistakes candidates make

• Using ROW_NUMBER (assigns unique ranks, picks only one on ties).
• Not using RANK/DENSE_RANK to handle ties correctly.
• Filtering before the window function is computed.
• Not joining Employee table to get experience_years.

Interview preparation tip

"Max per group with ties" requires RANK() or DENSE_RANK() (both handle ties equally), not ROW_NUMBER() (breaks ties arbitrarily). The window function approach is clean and handles all edge cases. Practice this pattern for: "top performers per department," "highest-rated product per category," "most recent order per customer." The PARTITION BY + ORDER BY + RANK pattern is the universal solution.