Coding questions · Amazon

Find Top-3 Salaries Per Department Using SQL

This question evaluates SQL data manipulation competency, focusing on group-wise aggregation, ordering, and selecting the top-N salary values within each department.

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.