Top Earners by Department

Medium

Your analytics team tracks compensation across business units. You are given two relational tables: Employee : employee records (id, name, salary, departmentId) Department : department metadata (id, name) Build a result set that returns every employee whose salary is the highest within their own department . Important rules: If multiple employees tie at the top salary for a department, include all of them. Matching must use department IDs (Employee.departmentId = Department.id). Departments with no employees should not produce any result rows. Return exactly these columns: Department Employee Salary Unless your local evaluator requires deterministic ordering, rows may be returned in any order. This problem should be solvable in both: SQL (SQLite 3.27.2) Pandas

Example 1

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |

Department:
| id | name  |
|----|-------|
| 1  | IT    |
| 2  | Sales |

Output

[
  {"Department":"IT","Employee":"Jim","Salary":90000},
  {"Department":"IT","Employee":"Max","Salary":90000},
  {"Department":"Sales","Employee":"Henry","Salary":80000}
]

Explanation

IT has a top-salary tie at 90000, so both Jim and Max are returned. Sales has a single top earner, Henry.

Example 2

Input

Employee:
| id | name   | salary | departmentId |
|----|--------|--------|--------------|
| 10 | Alice  | 120000 | 3            |
| 11 | Bob    | 95000  | 3            |

Department:
| id | name       |
|----|------------|
| 3  | Marketing  |
| 4  | Legal      |

Output

[
  {"Department":"Marketing","Employee":"Alice","Salary":120000}
]

Explanation

Only Marketing has employees in this input. Legal has no employees, so it contributes no output row.

Example 3

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 21 | Emma  | 50000  | 7            |
| 22 | Emma  | 70000  | 8            |
| 23 | Noah  | 70000  | 8            |

Department:
| id | name      |
|----|-----------|
| 7  | Support   |
| 8  | Support   |

Output

[
  {"Department":"Support","Employee":"Emma","Salary":50000},
  {"Department":"Support","Employee":"Emma","Salary":70000},
  {"Department":"Support","Employee":"Noah","Salary":70000}
]

Explanation

Departments are separated by ID, even when names are the same. Department 7 has top salary 50000, while department 8 has top salary 70000 with a tie.

Constraints

1 <= number of rows in Department <= 5,000
0 <= number of rows in Employee <= 50,000
Department.id is unique
Employee.id is unique
Employee.salary is an integer in [0, 1,000,000,000]
Department names are non-null and may repeat across different IDs
If Employee.departmentId references a missing department row, that employee should not appear after a correct join
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: Department, Employee, Salary

Employee: | id | name | salary | departmentId | |----|-------|--------|--------------| | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | Department: | id | name | |----|-------| | 1 | IT | | 2 | Sales |

Employee: | id | name | salary | departmentId | |----|--------|--------|--------------| | 10 | Alice | 120000 | 3 | | 11 | Bob | 95000 | 3 | Department: | id | name | |----|------------| | 3 | Marketing | | 4 | Legal |

Employee: | id | name | salary | departmentId | |----|-------|--------|--------------| | 21 | Emma | 50000 | 7 | | 22 | Emma | 70000 | 8 | | 23 | Noah | 70000 | 8 | Department: | id | name | |----|-----------| | 7 | Support | | 8 | Support |