Problem Description –
Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.
Return the result table in any order.
The query result format is in the following example.
Difficulty Level – Medium
Problem Link – https://leetcode.com/problems/project-employees-iii/
SELECT project_id, employee_id FROM ( SELECT p.project_id, p.employee_id, DENSE_RANK() OVER(PARTITION BY p.project_id ORDER BY e.experience_years DESC) as rnk FROM project as p JOIN employee as e ON p.employee_id = e.employee_id ) x WHERE rnk = 1
This is another variation of this problem that we solved previously. But here we have to first partition the data in the DENSE_RANK window function before finding the most experienced employees as we are asked to find so for every project.