This Leetcode problem Project Employees III LeetCode Solution is done in SQL.

List of all LeetCode Solution

Project Employees III LeetCode Solution

Problem Statement

Column Name	Type
project_id	int
employee_id	int

(project_id, employee_id) is the primary key of this table. employee_id is a foreign key to Employee table. Each row of this table indicates that the employee with employee_id is working on the project with project_id.

Column Name	Type
employee_id	int
name	varchar
experience_years	int

employee_id is the primary key of this table. It’s guaranteed that experience_years is not NULL. Each row of this table contains information about one employee.

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.

The query result format is in the following example:

Example 1:
Input:

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

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

Output:

project_id	employee_id
1	1
1	3
2	1

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.

Project Employees III LeetCode Solution MySQL

select 
  p.project_id, 
  e.employee_id 
from 
  (
    select 
      project_id, 
      max(experience_years) as max_years 
    from 
      Project as p 
      join Employee as e on p.employee_id = e.employee_id 
    group by 
      project_id
  ) as q, 
  Project as p, 
  Employee as e 
where 
  p.project_id = q.project_id 
  and p.employee_id = e.employee_id 
  and e.experience_years >= max_years;Code language: SQL (Structured Query Language) (sql)

Project Employees III LeetCode Solution MySQL (Another approach)

select 
  p.project_id, 
  e.employee_id 
from 
  Project as p, 
  Employee as e 
where 
  p.employee_id = e.employee_id 
  and (
    p.project_id, e.experience_years
  ) in (
    select 
      project_id, 
      max(experience_years) as experience_years 
    from 
      Project as p 
      join Employee as e on p.employee_id = e.employee_id 
    group by 
      project_id
  )Code language: SQL (Structured Query Language) (sql)