Project Employees III - Practice Coding Problems

Project Employees III - Problem

Database Medium

Project Employees III - Finding the Most Experienced Team Members

You are a project manager at a tech company, and you need to identify the most experienced employees working on each project. This is crucial for making important technical decisions and assigning leadership roles.

Problem: Given two database tables - Project (which maps employees to projects) and Employee (which contains employee details including experience), write a SQL query to find the most experienced employee(s) in each project.

Key Details:
• If multiple employees have the same maximum experience years in a project, return all of them
• The result should show project_id, employee_id, name, and experience_years
• Return results in any order

This problem tests your ability to work with window functions, joins, and ranking in SQL - essential skills for database management and analytics roles.

Input & Output

example_1.sql — Basic Case

$ 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 | Alice | 12 | | 2 | Bob | 7 | | 3 | Carol | 10 | | 4 | Dave | 12 |

› Output: | project_id | employee_id | name | experience_years | |------------|-------------|-------|------------------| | 1 | 1 | Alice | 12 | | 2 | 1 | Alice | 12 | | 2 | 4 | Dave | 12 |

💡 Note: In project 1, Alice has the maximum experience (12 years). In project 2, both Alice and Dave have maximum experience (12 years), so both are returned.

example_2.sql — Multiple Ties

$ Input: Project table: | project_id | employee_id | |------------|-------------| | 1 | 1 | | 1 | 2 | | 1 | 3 | Employee table: | employee_id | name | experience_years | |-------------|---------|------------------| | 1 | Alice | 10 | | 2 | Bob | 10 | | 3 | Charlie | 10 |

› Output: | project_id | employee_id | name | experience_years | |------------|-------------|---------|------------------| | 1 | 1 | Alice | 10 | | 1 | 2 | Bob | 10 | | 1 | 3 | Charlie | 10 |

💡 Note: All three employees have the same maximum experience (10 years) in project 1, so all are returned.

example_3.sql — Single Employee per Project

$ Input: Project table: | project_id | employee_id | |------------|-------------| | 1 | 1 | | 2 | 2 | | 3 | 3 | Employee table: | employee_id | name | experience_years | |-------------|-------|------------------| | 1 | Alice | 5 | | 2 | Bob | 8 | | 3 | Carol | 12 |

› Output: | project_id | employee_id | name | experience_years | |------------|-------------|-------|------------------| | 1 | 1 | Alice | 5 | | 2 | 2 | Bob | 8 | | 3 | 3 | Carol | 12 |

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

Constraints

1 ≤ project_id, employee_id ≤ 10⁴
employee_id in Project table exists in Employee table (foreign key constraint)
1 ≤ experience_years ≤ 50
1 ≤ name.length ≤ 20
Each (project_id, employee_id) combination in Project table is unique

Visualization

Tap to expand

Understanding the Visualization

Gather Participants

Collect all hackathon-participant pairs with their skill levels

Group by Hackathon

Organize participants into their respective hackathons

Rank by Skill

Within each hackathon, rank participants by experience level

Select Leaders

Choose all rank-1 participants as team leaders (handles ties naturally)

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY allow us to rank employees within each project independently, making it easy to handle ties and find all most experienced employees in a single, efficient query.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28 Apple 22

The optimal solution uses window functions with RANK() to efficiently find the most experienced employees in each project. The key insight is to partition data by project_id and rank by experience_years DESC, then filter for rank 1. This approach naturally handles ties and processes data in a single pass with O(n log n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Two-Pass with CTE	O(n)	O(p)	Use CTE to first calculate max experience per project, then join to filter
Window Function (Optimal)	O(n log n)	O(1)	Use window function to rank employees by experience within each project
Brute Force (Correlated Subquery)	O(n²)	O(1)	Use correlated subquery to find max experience for each project separately

Two-Pass with CTE — Algorithm Steps

Create CTE to join Project and Employee tables
Create second CTE to find maximum experience years per project
Join the CTEs to filter employees with maximum experience in their projects
Return the filtered results with all required columns

Visualization

Tap to expand

Step-by-Step Walkthrough

First CTE

Join tables to create project_employees view

Second CTE

Calculate max experience per project

Final Join

Join CTEs to filter employees with max experience

Result

Return all most experienced employees per project

Code -

solution.c — C

// SQL solution using CTE (Common Table Expression)
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
        "WITH project_employees AS ( "
        "    SELECT p.project_id, p.employee_id, e.name, e.experience_years "
        "    FROM Project p JOIN Employee e ON p.employee_id = e.employee_id "
        "), "
        "max_experience_per_project AS ( "
        "    SELECT project_id, MAX(experience_years) as max_experience "
        "    FROM project_employees GROUP BY project_id "
        ") "
        "SELECT pe.project_id, pe.employee_id, pe.name, pe.experience_years "
        "FROM project_employees pe "
        "JOIN max_experience_per_project mep "
        "    ON pe.project_id = mep.project_id "
        "    AND pe.experience_years = mep.max_experience "
        "ORDER BY pe.project_id, pe.employee_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Two linear passes through the data - once for aggregation, once for joining

✓ Linear Growth

Space Complexity

O(p)

Space proportional to number of projects for storing max experience values

✓ Linear Space

52.3K Views

High Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Two-Pass with CTE — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler