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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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