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 per ProjectProject Tableproject_idemployee_id111221Employee Tableemployee_idnameexperience1Khaled32Ali2JOIN & RANKResult: Most Experiencedproject_idemployee_idnameexp11Khaled321Khaled3Window Function: RANK() OVER (PARTITION BY project_id ORDER BY experience_years DESC)
Understanding the Visualization
1
Join Tables
Combine Project and Employee data
2
Rank by Experience
Use window function to rank within each project
3
Filter Top Ranks
Keep only employees with rank 1 (most experienced)
Key Takeaway
🎯 Key Insight: Use window functions with PARTITION BY to find maximum values within groups while handling ties correctly

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