# LeetCode 1077: Project Employees III

## Problem Restatement

Using `Project(project_id, employee_id)` and `Employee(employee_id, name, experience_years)`, find the most experienced employee for each project.

If there are ties, return all tied employees.

## Edge Cases

- Match the aggregate to the question: use raw `COUNT` or `SUM` for rows/amounts, and `COUNT(DISTINCT ...)` only for unique entities.
- Tie cases should return every qualifying row unless the statement asks for a single row.
- For outer joins, keep filters on the joined table in the `ON` clause when unmatched rows must remain visible.

## Common Pitfalls

- Do not put aggregate filters in `WHERE`; use `HAVING` after `GROUP BY`.
- When the answer needs all tied winners, avoid `LIMIT 1` unless the statement explicitly asks for one row.

## Implementation

```sql
SELECT p.project_id, p.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
);
```

## Alternative with Window Function

```sql
WITH ranked AS (
    SELECT p.project_id, p.employee_id,
           RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) AS rk
    FROM Project p
    JOIN Employee e ON p.employee_id = e.employee_id
)
SELECT project_id, employee_id
FROM ranked
WHERE rk = 1;
```

## Code Explanation

The subquery finds the maximum experience years per project.

The outer query selects employees whose `(project_id, experience_years)` pair matches the maximum for their project.

The window function approach ranks employees within each project by experience descending and selects rank 1 (handles ties automatically).