Топ-3 уникальных зарплат по департаментам
Даны таблицы Employee и Department.
Нужно вывести всех сотрудников, зарплата которых входит в top-3 уникальных зарплат внутри их департамента.
Если несколько сотрудников получают одинаковую зарплату, они должны получить одинаковый ранг и все попасть в результат, если эта зарплата входит в top-3 уникальных зарплат департамента.
Схема
CREATE TABLE Department (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE Employee (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
salary INTEGER NOT NULL,
departmentId INTEGER NOT NULL,
FOREIGN KEY (departmentId) REFERENCES Department(id)
);Формат вывода
Верните колонки:
Department— название департамента;Employee— имя сотрудника;Salary— зарплата.
Отсортируйте результат по Department ASC, Salary DESC, Employee ASC.
Важный нюанс
top-3 уникальных зарплат означает, что для зарплат 200, 200, 100, 50 ранги должны быть 1, 1, 2, 3. Для этого нужен DENSE_RANK(), а не ROW_NUMBER().Решение прямо на странице
Напишите код, запустите проверки и только потом открывайте разбор.
Нажмите «Запустить проверки» или Ctrl+Enter.
Показать разбор
Подсказки
- Top-3 уникальных зарплат
Нужна функция, которая дает одинаковый ранг одинаковым зарплатам и не пропускает следующий ранг.
- DENSE_RANK
Используйте DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC).
- CTE
Удобно сначала посчитать ранги в WITH ranked AS (...), а потом отфильтровать salary_rank <= 3.
Идея решения
Задача сводится к ранжированию зарплат внутри каждого департамента.
Здесь нельзя использовать ROW_NUMBER(): если два сотрудника получают одинаковую зарплату, ROW_NUMBER() присвоит им разные номера. Нам нужны top-3 уникальных зарплат, поэтому одинаковые зарплаты должны иметь одинаковый ранг.
DENSE_RANK() дает именно такое поведение:
200, 200, 100, 50->1, 1, 2, 3;RANK()дал бы1, 1, 3, 4;ROW_NUMBER()дал бы1, 2, 3, 4.
После ранжирования оставляем строки с salary_rank <= 3 и выводим только требуемые колонки.
Эталонный код
WITH ranked AS (
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary,
DENSE_RANK() OVER (
PARTITION BY e.departmentId
ORDER BY e.salary DESC
) AS salary_rank
FROM Employee e
JOIN Department d
ON e.departmentId = d.id
)
SELECT
Department,
Employee,
Salary
FROM ranked
WHERE salary_rank <= 3
ORDER BY Department ASC, Salary DESC, Employee ASC;