LeetCode 1077. Project Employees III SQL Solution

Problem

LeetCode SQL Problem

1077. Project Employees III

Project table

project_id	employee_id
1	1
1	2
1	3
2	1
2	4

Employee table

employee_id	name	experience_years
1	Khaled	3
2	Ali	2
3	John	3
4	Doe	2

Solution - Rank() Window Function

MySQL

Most experienced employees in each project

-- Use Rank() function to rank most experience employees in each project as 1
WITH ProjEmpExperienceRanked
AS (
	SELECT P.project_id
		,E.employee_id
		,rank() OVER (
			PARTITION BY P.project_id ORDER BY E.experience_years DESC
			) AS rank_num
	FROM Project AS P
	INNER JOIN Employee AS E ON P.employee_id = E.employee_id
	)
-- Report all employees with the maximum number of experience years in each project in case a tie
SELECT project_id
	,employee_id
FROM ProjEmpExperienceRanked
WHERE rank_num = 1

TSQL

Most experienced employees in each project

-- Use Rank() function to rank most experience employees in each project as 1
WITH ProjEmpExperienceRanked
AS (
	SELECT P.project_id
		,E.employee_id
		,rank() OVER (
			PARTITION BY P.project_id ORDER BY E.experience_years DESC
			) AS rank_num
	FROM Project AS P
	INNER JOIN Employee AS E ON P.employee_id = E.employee_id
	)
-- Report all employees with the maximum number of experience years in each project in case a tie
SELECT project_id
	,employee_id
FROM ProjEmpExperienceRanked
WHERE rank_num = 1

Query Output

project_id	employee_id
1	1
1	3
2	1

Solution - CTE + Group By

MySQL

Most experienced employees in each project

-- Use Group By to find out maximum experience years in each project
WITH ProjEmpMaxExperienceYear
AS (
	SELECT P.project_id
		,max(E.experience_years) AS max_experience_years
	FROM Project AS P
	INNER JOIN Employee AS E ON P.employee_id = E.employee_id
	GROUP BY P.project_id
	)
-- Report all employees with the maximum number of experience years in each project in case a tie
SELECT P.project_id
	,P.employee_id
FROM Project AS P
INNER JOIN Employee AS E ON P.employee_id = E.employee_id
INNER JOIN ProjEmpMaxExperienceYear AS ME ON P.project_id = ME.project_id
	AND E.experience_years = ME.max_experience_years

TSQL

Most experienced employees in each project

-- Use Group By to find out maximum experience years in each project
WITH ProjEmpMaxExperienceYear
AS (
	SELECT P.project_id
		,max(E.experience_years) AS max_experience_years
	FROM Project AS P
	INNER JOIN Employee AS E ON P.employee_id = E.employee_id
	GROUP BY P.project_id
	)
-- Report all employees with the maximum number of experience years in each project in case a tie
SELECT P.project_id
	,P.employee_id
FROM Project AS P
INNER JOIN Employee AS E ON P.employee_id = E.employee_id
INNER JOIN ProjEmpMaxExperienceYear AS ME ON P.project_id = ME.project_id
	AND E.experience_years = ME.max_experience_years

Query Output

project_id	employee_id
1	1
1	3
2	1