1077 Project Employees Iii

Description

Table: Project

• (project_id, employee_id) is the primary key (combination of columns with unique values) of this table.
• employee_id is a foreign key (reference column) to Employee table.
• Each row of this table indicates that the employee with employee_id is working on the project with project_id.

Table: Employee

• employee_id is the primary key (column with unique values) of this table.
• Each row of this table contains information about one employee.

Problem Statement

Write a solution to report 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 result format is in the following example.

Example 1:

Input: Project table:

Employee table:

Output:

Explanation:

• Both employees with id 1 and 3 have the most experience among the employees of the first project. For the second project, the employee with id 1 has the most experience.

Solution

The problem is asking for the most experienced employees in each project. This can be solved using window functions easily.

Approach 1: Using Window Functions

You can create a partition for each project using project_id column and order the records using experience_years column. This will give you the employees with maximum experience in each project. Now, in case of a tie, you want to report all employees with same experience. This means you need to use RANK() or DENSE_RANK() for getting the most experienced employee per project.

1. Find employees for each project. For this, you need to join Project table with Employee table.

1SELECT 
2    p.project_id, e.employee_id, e.name, e.experience_years
3    FROM Project p
4    JOIN Employee e
5    ON p.employee_id = e.employee_id;

2. Use PARTITION BY clause to create a partition for each project and order the results for each partition using experience_years column. Use RANK() or DENSE_RANK() window function to get the most experienced employee per project.

1DENSE_RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) ranking

For example, below query will give you ranking for each project_id.

1SELECT 
2    p.project_id, e.employee_id, e.name, e.experience_years,
3    DENSE_RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) ranking
4    FROM Project p
5    JOIN Employee e
6    ON p.employee_id = e.employee_id;

3. Create a temporary view from above query and Filter the results to get the employees with maximum experience in each project. This can be done using WHERE ranking = 1 clause.

1SELECT project_id, employee_id
2    FROM ranked_employees
3    WHERE ranking = 1;

The final solution looks like this.

 1WITH ranked_employees AS (
 2    SELECT 
 3        p.project_id, e.employee_id, e.name, e.experience_years,
 4        DENSE_RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) ranking
 5        FROM Project p
 6        JOIN Employee e
 7        ON p.employee_id = e.employee_id
 8) SELECT project_id, employee_id
 9    FROM ranked_employees
10    WHERE ranking = 1;