Project Assignment Seniority Leads

Medium

A delivery organization keeps two relational tables: Project project_id employee_id Each row means the employee is staffed on that project. Employee employee_id name experience_years Task: For each project_id, find the highest experience_years among assigned employees. Return every assignment row (project_id, employee_id) where the employee reaches that project maximum. If multiple employees tie at the highest experience for a project, include all tied rows. Return exactly these columns: project_id employee_id Result order can be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 10         | 1           |
| 10         | 2           |
| 10         | 3           |
| 20         | 1           |
| 20         | 4           |

Employee:
| employee_id | name  | experience_years |
|-------------|-------|------------------|
| 1           | Riya  | 5                |
| 2           | Omar  | 3                |
| 3           | Nia   | 5                |
| 4           | Chen  | 2                |

Output

[
  {"project_id":10,"employee_id":1},
  {"project_id":10,"employee_id":3},
  {"project_id":20,"employee_id":1}
]

Explanation

Project 10 has a max of 5 years, achieved by employees 1 and 3. Project 20 has max 5 years, achieved by employee 1 only.

Example 2

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 31         | 100         |
| 31         | 101         |
| 31         | 102         |

Employee:
| employee_id | name | experience_years |
|-------------|------|------------------|
| 100         | A    | 7                |
| 101         | B    | 7                |
| 102         | C    | 7                |

Output

[
  {"project_id":31,"employee_id":100},
  {"project_id":31,"employee_id":101},
  {"project_id":31,"employee_id":102}
]

Explanation

All assigned employees have the same experience value, so every row is part of the result.

Example 3

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 50         | 9           |
| 50         | 8           |
| 60         | 8           |
| 60         | 7           |

Employee:
| employee_id | name | experience_years |
|-------------|------|------------------|
| 7           | T    | 11               |
| 8           | U    | 4                |
| 9           | V    | 15               |

Output

[
  {"project_id":50,"employee_id":9},
  {"project_id":60,"employee_id":7}
]

Explanation

Project 50 is led by employee 9 (15 years). Project 60 is led by employee 7 (11 years).

Constraints

1 <= number of rows in Project <= 10,000
1 <= number of rows in Employee <= 10,000
1 <= project_id <= 1,000,000,000
1 <= employee_id <= 1,000,000,000
0 <= experience_years <= 60
(project_id, employee_id) pairs are unique
Each Project.employee_id references an existing Employee.employee_id
Each project has at least one assigned employee
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return columns: project_id, employee_id

Project: | project_id | employee_id | |------------|-------------| | 10 | 1 | | 10 | 2 | | 10 | 3 | | 20 | 1 | | 20 | 4 | Employee: | employee_id | name | experience_years | |-------------|-------|------------------| | 1 | Riya | 5 | | 2 | Omar | 3 | | 3 | Nia | 5 | | 4 | Chen | 2 |

Project: | project_id | employee_id | |------------|-------------| | 31 | 100 | | 31 | 101 | | 31 | 102 | Employee: | employee_id | name | experience_years | |-------------|------|------------------| | 100 | A | 7 | | 101 | B | 7 | | 102 | C | 7 |

Project: | project_id | employee_id | |------------|-------------| | 50 | 9 | | 50 | 8 | | 60 | 8 | | 60 | 7 | Employee: | employee_id | name | experience_years | |-------------|------|------------------| | 7 | T | 11 | | 8 | U | 4 | | 9 | V | 15 |