HardPro challengeSQL
Top 2 Salaries per Department
SQLDatabases
Write a SQL query that returns the top 2 highest-paid employees in each
department, ordered by department name then salary descending.
Table: employees
| id | name | salary | dept |
|---|---|---|---|
| 1 | Alice | 90000 | Engineering |
| 2 | Bob | 75000 | Engineering |
| 3 | Carol | 60000 | Engineering |
| 4 | Dave | 80000 | Marketing |
| 5 | Eve | 70000 | Marketing |
| 6 | Frank | 65000 | Marketing |
Expected output (columns: name, dept, salary)
Alice|Engineering|90000
Bob|Engineering|75000
Dave|Marketing|80000
Eve|Marketing|70000Hint — assign a row number per department with
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC), then filterrows where that number ≤ 2.
Sample tests
Test #12 depts × top-2 each = 4 rows
Input: "CREATE TABLE employees (id INTEGER, name TEXT, salary INTEGER, dept TEXT);\nINSERT INTO employees VALUES\n (1, 'Alice', 90000, 'Engineering'),\n (2, 'Bob', 75000, 'Engineering'),\n (3, 'Carol', 60000, 'Engineering'),\n (4, 'Dave', 80000, 'Marketing'),\n (5, 'Eve', 70000, 'Marketing'),\n (6, 'Frank', 65000, 'Marketing');"
Output: "Alice|Engineering|90000\nBob|Engineering|75000\nDave|Marketing|80000\nEve|Marketing|70000"