Project Employees III - Problem

You are given two tables: Project and Employee.

The Project table shows which employees are working on which projects, while the Employee table contains information about each employee including their experience years.

Write a SQL query to find the most experienced employees in each project. If there are multiple employees with the same maximum experience years in a project, include all of them in the result.

Return the result in any order.

Table Schema

Project
Column Name Type Description
project_id PK int Project identifier
employee_id PK int Employee identifier working on the project
Primary Key: project_id, employee_id
Employee
Column Name Type Description
employee_id PK int Unique employee identifier
name varchar Employee name
experience_years int Years of experience
Primary Key: employee_id

Input & Output

Example 1 — Multiple Projects with Different Max Experience
Input Tables:
Project
project_id employee_id
1 1
1 2
1 3
2 1
2 4
Employee
employee_id name experience_years
1 Khaled 3
2 Ali 2
3 John 1
4 Doe 2
Output:
project_id employee_id name experience_years
1 1 Khaled 3
2 1 Khaled 3
💡 Note:

In project 1, Khaled has 3 years of experience, Ali has 2, and John has 1. Khaled is the most experienced. In project 2, Khaled (3 years) and Doe (2 years) are working, so Khaled is again the most experienced.
Example 2 — Tie in Experience Years
Input Tables:
Project
project_id employee_id
1 1
1 2
1 3
Employee
employee_id name experience_years
1 Alice 5
2 Bob 5
3 Charlie 3
Output:
project_id employee_id name experience_years
1 1 Alice 5
1 2 Bob 5
💡 Note:

In project 1, both Alice and Bob have 5 years of experience, which is the maximum. Since there's a tie, both employees are included in the result.
Example 3 — Single Employee per Project
Input Tables:
Project
project_id employee_id
1 1
2 2
Employee
employee_id name experience_years
1 David 2
2 Eve 4
Output:
project_id employee_id name experience_years
1 1 David 2
2 2 Eve 4
💡 Note:

Each project has only one employee, so each employee is automatically the most experienced in their respective project.

Constraints

  • 1 ≤ project_id ≤ 100
  • 1 ≤ employee_id ≤ 10000
  • 1 ≤ name.length ≤ 20
  • 1 ≤ experience_years ≤ 50

Visualization

Tap to expand
Project Employees III Find Most Experienced Employees per Project INPUT Project Table project_id employee_id 1 1 1 2 1 3 2 1 2 4 Employee Table emp_id name exp_yrs 1 Alice 3 2 Bob 2 3 Carol 3 4 Dan 5 ALGORITHM STEPS 1 JOIN Tables Project JOIN Employee ON employee_id 2 Find MAX Experience Per project, calculate MAX(experience_years) 3 Window Function RANK() OVER (PARTITION BY project_id) 4 Filter Top Ranked WHERE rank = 1 Handles ties properly SELECT project_id, employee_id FROM (... RANK() ...) WHERE rnk = 1 FINAL RESULT project_id emp_id 1 1 (Alice) 3 (Carol) 2 4 (Dan) Explanation: Project 1: Alice and Carol both have 3 years (tie) Project 2: Dan has max experience (5 years) Project 1 A C 3 yrs each Project 2 D 5 yrs Key Insight: Use RANK() or DENSE_RANK() window function to handle ties properly. Unlike MAX() with GROUP BY, window functions preserve all rows while computing aggregates, allowing us to return ALL employees with maximum experience when there are ties. Subquery pattern: compute rank, then filter where rank = 1. TutorialsPoint - Project Employees III | Optimal Solution

Related Problems

Asked in
Amazon 23 Facebook 18 Microsoft 15
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen