Find Top-3 Salaries Per Department Using SQL
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
employees
+----+---------+--------+---------+
| id | name | salary | dept_id |
+----+---------+--------+---------+
| 1 | Alice | 120000 | 10 |
| 2 | Bob | 90000 | 10 |
| 3 | Charlie | 115000 | 20 |
| 4 | David | 130000 | 10 |
| 5 | Eve | 110000 | 20 |
+----+---------+--------+---------+
##### Scenario
Employee compensation analysis across departments
##### Question
Given an employees table with salary and department columns, write SQL to return the top-3 salary amounts in each department (it’s okay if fewer than three employees tie for a salary amount).
##### Hints
Use window functions (ROW_NUMBER/RANK/DENSE_RANK) or MAX subqueries to isolate the three highest salaries per department.
Overview: This question evaluates SQL data manipulation competency, focusing on group-wise aggregation, ordering, and selecting the top-N salary values within each department.
Given an employees table with salary and department columns, write SQL to return the top-3 salary amounts in each department (distinct salary amounts per department; return fewer if fewer exist).
Tables
employees(id INTEGER, name VARCHAR(100), salary INTEGER, dept_id INTEGER)
Hints
- Use DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) to rank distinct salary amounts and filter ranks <= 3.
- Alternatively, select DISTINCT dept_id, salary and use a correlated subquery or a MAX-based iterative approach to isolate the top three salary amounts per department.