Table: Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(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
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id is the primary key (column with unique values) of this table. Each row of this table contains information about one employee.
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: +-------------+-------------+ | 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 | +-------------+--------+------------------+ 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.
Problem Overview: Each project can have multiple employees assigned. The task is to return the employee (or employees if there is a tie) with the highest experience_years for every project_id. The data comes from two tables: Project and Employee, so the solution requires joining them and comparing experience values within each project group.
Approach 1: Correlated Subquery (O(n^2) time, O(1) space)
Join the Project and Employee tables and filter rows where the employee's experience equals the maximum experience within the same project. The maximum can be found using a correlated subquery that scans employees linked to that project. While this works logically, the database may evaluate the subquery repeatedly for each row, which increases cost on larger datasets. This method is mostly useful when window functions are unavailable.
Approach 2: Group By + Join Back (O(n log n) time, O(n) space)
First compute the maximum experience per project using GROUP BY project_id. Then join this aggregated result back with the joined Project and Employee dataset. The join condition matches both project_id and the calculated maximum experience value. This approach avoids repeated subqueries and uses standard SQL aggregation. It works across most relational databases and scales better than the correlated subquery.
Approach 3: Inner Join + Window Function (O(n log n) time, O(n) space)
Join Project and Employee first, then apply a window function such as RANK() or DENSE_RANK() partitioned by project_id and ordered by experience_years descending. The window function assigns rank 1 to the most experienced employees inside each project partition. Filter the result where rank equals 1. This method keeps the query compact and avoids extra joins or nested queries. Modern SQL engines optimize window functions well, making this the cleanest and most maintainable solution.
Concepts involved include relational joins from database queries, partition-based ranking from window functions, and standard SQL aggregation logic.
Recommended for interviews: The window function solution using RANK() is typically expected. It clearly demonstrates that you understand partitioning and ranking operations in SQL. Showing the GROUP BY approach first proves you understand aggregation, while the window function version highlights stronger SQL proficiency.
We can first perform an inner join between the Project table and the Employee table, and then use the window function rank() to group the Project table, sort it in descending order by experience_years, and finally select the most experienced employee for each project.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery | O(n^2) | O(1) | Simple logic when window functions or advanced joins are unavailable |
| Group By + Join Back | O(n log n) | O(n) | Portable SQL solution supported by most relational databases |
| Inner Join + Window Function | O(n log n) | O(n) | Best readability and modern SQL approach using partitioned ranking |
LeetCode Medium 1077 Interview SQL Question with Detailed Explanation • Everyday Data Science • 2,996 views views
Watch 3 more video solutions →Practice Project Employees III with our built-in code editor and test cases.
Practice on FleetCode