SQL Interview Questions – Project Employees III

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/

Solution –

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.