Project Assignment Seniority Leads

MEDIUM20 pts

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

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

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

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).

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

project =

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

employee =

[{"employee_id":1,"name":"Riya","experience_years":5},{"employee_id":2,"name":"Omar","experience_years":3},{"employee_id":3,"name":"Nia","experience_years":5},{"employee_id":4,"name":"Chen","experience_years":2}]

Project Assignment Seniority Leads

Hints

Project Assignment Seniority Leads

Hints