Department High-Earner Bands

Hard

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 1

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 2

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 3

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.

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

Loading problem...

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

Department High-Earner Bands

Hard

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 1

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 2

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 3

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.

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