[MEDIUM] #1077 - Project Employees III

[hackdartstorm]

🎯 Problem: Project Employees III

📖 The Real Problem

You have two tables:

Project table:

• project_id (int)
• employee_id (int)

Employee table:

• employee_id (int)
• name (varchar)
• experience_years (int)

Your task: Find the employee(s) with the MOST experience years in EACH project.

The Challenge:

• Multiple employees might have the same maximum experience in a project
• You need to return ALL employees who have the maximum (not just one)
• This requires grouping, aggregation, and filtering

Why this problem exists:

• Real-world scenario: finding top performers per team
• Tests understanding of SQL subqueries and JOINs
• Requires handling ties correctly (multiple winners)

💡 Why This Matters

Real-world applications:

• HR Analytics - Finding senior employees per department
• Project Management - Identifying most experienced team members
• Team Building - Balancing experience across projects
• Resource Allocation - Assigning mentors based on experience

Skills you'll develop:

• ✅ SQL JOIN operations
• ✅ GROUP BY with aggregations
• ✅ Subqueries / CTEs (Common Table Expressions)
• ✅ Handling ties in rankings
• ✅ MAX() with GROUP BY

📋 Contributor Tasks

Step 1: Understand the Data

1. Examine the Project table structure
2. Examine the Employee table structure
3. Understand the relationship (many-to-many through employee_id)
4. Identify what "most experienced" means (MAX experience_years)

Step 2: Plan Your Approach

Approach 1: Subquery

1. For each project, find the MAX experience_years
2. Join back to get employees with that experience

Approach 2: Window Function (Advanced)

1. Use RANK() or DENSE_RANK() partitioned by project
2. Filter for rank = 1

Step 3: Implement the Solution

1. Write the JOIN between Project and Employee
2. Group by project_id
3. Find MAX experience_years per project
4. Filter to keep only employees with MAX experience

Step 4: Test Your Solution

1. Test with single employee per project
2. Test with ties (multiple employees with same max experience)
3. Test with empty tables
4. Test with single project

✅ Expected Outcome

SQL Query Structure:

SELECT 
    p.project_id,
    e.employee_id,
    e.experience_years
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
WHERE (p.project_id, e.experience_years) IN (
    SELECT project_id, MAX(experience_years)
    FROM Project
    JOIN Employee ON Project.employee_id = Employee.employee_id
    GROUP BY project_id
)

Expected Behavior:

• ✅ Returns ALL employees with max experience per project
• ✅ Handles ties correctly (multiple rows for same project)
• ✅ Includes project_id, employee_id, and experience_years
• ✅ Works with single or multiple projects
• ✅ Handles edge cases (empty tables, single employee)

Example Test 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           | Khaled | 3                |
-- | 2           | Ali    | 2                |
-- | 3           | John   | 3                |
-- | 4           | Doe    | 4                |
-- +-------------+--------+------------------+

-- Output:
-- +-------------+---------------+
-- | project_id  | employee_id   |
-- +-------------+---------------+
-- | 1           | 1             |  -- 3 years (tied with John)
-- | 1           | 3             |  -- 3 years (tied with Khaled)
-- | 2           | 4             |  -- 4 years (highest)
-- +-------------+---------------+

📚 Additional Context & References

Understanding the Problem

Key Insight:
This is a "GROUP BY with MAX and ties" problem. You can't just use GROUP BY and MAX() because you need ALL columns, not just the aggregate.

Common Mistake:

-- WRONG: This loses employee_id information
SELECT project_id, MAX(experience_years)
FROM Project JOIN Employee ...
GROUP BY project_id

Solution Approaches

Approach 1: Subquery with IN (Recommended for beginners)

SELECT p.project_id, e.employee_id
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
WHERE (p.project_id, e.experience_years) IN (
    SELECT p2.project_id, MAX(e2.experience_years)
    FROM Project p2
    JOIN Employee e2 ON p2.employee_id = e2.employee_id
    GROUP BY p2.project_id
)

Approach 2: JOIN with Subquery

SELECT p.project_id, e.employee_id
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
JOIN (
    SELECT p2.project_id, MAX(e2.experience_years) as max_exp
    FROM Project p2
    JOIN Employee e2 ON p2.employee_id = e2.employee_id
    GROUP BY p2.project_id
) max_exp_per_project
ON p.project_id = max_exp_per_project.project_id
AND e.experience_years = max_exp_per_project.max_exp

Approach 3: Window Function (Advanced)

SELECT project_id, employee_id
FROM (
    SELECT p.project_id, e.employee_id,
           RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) as rnk
    FROM Project p
    JOIN Employee e ON p.employee_id = e.employee_id
) ranked
WHERE rnk = 1

Hints (Use Only If Stuck!)

💡 Hint 1

First, find the maximum experience years for each project. Then, find which employees have that experience.

💡 Hint 2

You'll need a subquery to find the MAX per project, then JOIN or filter based on that result.

💡 Hint 3

Remember: multiple employees can have the same maximum experience in a project. Don't use LIMIT 1!

Complexity Analysis

Time Complexity: O(n log n) or O(n²)

• JOIN operation: O(n²) worst case, O(n log n) with indexes
• GROUP BY: O(n log n) for sorting
• Subquery: Depends on approach

Space Complexity: O(n)

• Temporary tables for JOIN and GROUP BY
• Result set storage

Edge Cases to Consider

1. Empty tables: Return empty result
2. Single employee per project: That employee is the answer
3. All same experience: Return all employees
4. Multiple projects: Handle each independently
5. NULL values: Handle NULL experience_years

Related Problems

Once you solve this, try:

• Project Employees I - Basic JOIN
• Project Employees II - COUNT and GROUP BY
• Department Highest Salary - Similar pattern
• Rank Scores - Window functions

Helpful Resources

• SQL JOIN Tutorial
• SQL GROUP BY
• SQL Subqueries
• LeetCode 1077 - Project Employees III

📝 Notes

• Return ALL employees with maximum experience (handle ties)
• Output columns: project_id, employee_id
• experience_years is guaranteed to be non-NULL
• Each project can have multiple employees
• Experience years are integers

---

Ready to contribute?

1. Fork the repository
2. Create your SQL solution file
3. Test with provided examples
4. Submit a pull request!

File Location: exercises/1000_programs/medium/1077_project_employees_iii.sql

🚀 Happy coding!