Department High-Earner Bands

HARD30 pts

You are given two relational tables:

Employee: id, name, salary, departmentId
Department: id, name

For each department, identify salary bands using unique salary values sorted from high to low. An employee is classified as a high earner if their salary belongs to one of the top three unique salary bands inside that employee's department.

Important details:

Ranking is based on distinct salary values, not employee count.
If multiple employees share a qualifying salary, all of them must be returned.
If a department has fewer than three unique salaries, return all employees that match the available salary bands.

Return exactly these columns:

Department
Employee
Salary

The result can be returned in any order.

This problem should be solvable in both:

SQL (SQLite 3.27.2)
Pandas

Example

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 1  | Mina  | 96000  | 10           |
| 2  | Theo  | 91000  | 10           |
| 3  | Aria  | 91000  | 10           |
| 4  | Zane  | 87000  | 10           |
| 5  | Lila  | 82000  | 10           |
| 6  | Omar  | 73000  | 20           |
| 7  | Noor  | 61000  | 20           |

Department:
| id | name      |
|----|-----------|
| 10 | Platform  |
| 20 | Support   |

Output

[
  {"Department":"Platform","Employee":"Mina","Salary":96000},
  {"Department":"Platform","Employee":"Theo","Salary":91000},
  {"Department":"Platform","Employee":"Aria","Salary":91000},
  {"Department":"Platform","Employee":"Zane","Salary":87000},
  {"Department":"Support","Employee":"Omar","Salary":73000},
  {"Department":"Support","Employee":"Noor","Salary":61000}
]

Explanation

Platform has unique salaries 96000, 91000, 87000, 82000. Only the first three salary levels qualify, so Lila is excluded. Support has only two unique salaries, so both employees qualify.

Example

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 8  | Ravi  | 120000 | 30           |
| 9  | Inez  | 120000 | 30           |
| 10 | Kira  | 120000 | 30           |

Department:
| id | name      |
|----|-----------|
| 30 | Security  |

Output

[
  {"Department":"Security","Employee":"Ravi","Salary":120000},
  {"Department":"Security","Employee":"Inez","Salary":120000},
  {"Department":"Security","Employee":"Kira","Salary":120000}
]

Explanation

There is a single unique salary band, so everyone in that department is part of the top three distinct salary bands.

Example

Input

Employee:
| id | name   | salary | departmentId |
|----|--------|--------|--------------|
| 11 | Elena  | 150000 | 40           |
| 12 | Bruno  | 140000 | 40           |
| 13 | Gita   | 130000 | 40           |
| 14 | Pavel  | 120000 | 40           |

Department:
| id | name         |
|----|--------------|
| 40 | Data Science |

Output

[
  {"Department":"Data Science","Employee":"Elena","Salary":150000},
  {"Department":"Data Science","Employee":"Bruno","Salary":140000},
  {"Department":"Data Science","Employee":"Gita","Salary":130000}
]

Explanation

Only employees within the top three distinct salary bands are returned. Pavel is in the fourth distinct salary band and is excluded.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Employee <= 50,000
1 <= number of rows in Department <= 2,000
Employee.id is unique
Department.id is unique
1 <= Employee.salary <= 1,000,000,000
Employee.departmentId references Department.id in valid datasets
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: Department, Employee, Salary

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employee =

[{"id":1,"name":"Mina","salary":96000,"departmentId":10},{"id":2,"name":"Theo","salary":91000,"departmentId":10},{"id":3,"name":"Aria","salary":91000,"departmentId":10},{"id":4,"name":"Zane","salary":87000,"departmentId":10},{"id":5,"name":"Lila","salary":82000,"departmentId":10},{"id":6,"name":"Omar","salary":73000,"departmentId":20},{"id":7,"name":"Noor","salary":61000,"departmentId":20}]

department =

[{"id":10,"name":"Platform"},{"id":20,"name":"Support"}]

Department High-Earner Bands

Hints

Department High-Earner Bands

Hints