🏦 Задача с собеса в Т-банк на SQL: вторая зарплата в отделе
Условие: есть таблица сотрудников — отдел, имя, зарплата. Найди вторую по величине зарплату в каждом отделе.
Все понимают, что тут нужна оконная функция.
Но какую именно взять: на одинаковых зарплатах три похожие функции дают три разных ответа.
1️⃣ Данные
dept name salary
продажи Аня 150
продажи Боря 150
продажи Вика 120
продажи Гоша 100
IT Дима 200
IT Ева 180
IT Женя 160
Зарплаты в тысячах. У Ани и Бори одинаково, по 150 — вся задача про них.
2️⃣ Сначала уточни у интервьюера
«Вторая по величине» — это второй человек или второе значение зарплаты? В продажах второй человек получает 150, а второе значение — 120.
Обычно имеют в виду значение. Значит, правильный ответ:
продажи 120
IT 180
Задать этот вопрос вслух — уже плюс на собесе.
3️⃣ Что делает оконная функция
GROUP BY схлопывает отдел в одну строку. Оконная функция оставляет все 7 строк и дописывает рядом номер внутри отдела.
WITH r AS (
SELECT *,
ФУНКЦИЯ() OVER (
PARTITION BY dept
ORDER BY salary DESC
) AS n
FROM employees
)
SELECT dept, name, salary
FROM r WHERE n = 2;
PARTITION BY — нумеруем отдельно в каждом отделе. ORDER BY salary DESC — от большей зарплаты к меньшей. Фильтр по n стоит во внешнем запросе: оконные функции считаются после WHERE, поэтому в WHERE того же запроса n ещё не существует.
Осталось выбрать функцию.
4️⃣ ROW_NUMBER — мимо
Нумерует подряд, одинаковые или нет:
Аня 150 1
Боря 150 2 ← n = 2
Вика 120 3
Гоша 100 4
В продажах n = 2 у Бори, а у него 150 — это та же максимальная. Вдобавок кому из Ани и Бори достанется 1, база решает сама, и от запуска к запуску это может поменяться.
5️⃣ RANK — тоже мимо
Одинаковым даёт один номер, но следующий пропускает:
Аня 150 1
Боря 150 1
Вика 120 3 ← номер 2 пропущен
Гоша 100 4
Номера 2 в продажах нет — отдел просто пропал из ответа.
6️⃣ DENSE_RANK — то, что нужно
Одинаковым даёт один номер и следующий не пропускает:
Аня 150 1
Боря 150 1
Вика 120 2 ← n = 2
Гоша 100 3
Продажи — 120, IT — 180. Ровно то, что искали.
7️⃣ Финальный запрос
WITH r AS (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY dept
ORDER BY salary DESC
) AS n
FROM employees
)
SELECT dept, name, salary
FROM r WHERE n = 2;
-- продажи 120, IT 180
Итог: 🤩
На одинаковых зарплатах ROW_NUMBER берёт ту же максимальную, RANK теряет отдел, DENSE_RANK даёт правильный ответ.
❤️ Поддержать канал бустами, чтобы у автора появился дополнительный функционал можно - здесь (это бесплатно и доступно с подпиской telegram premium
✔️ Подпишитесь на канал, чтобы не пропустить следующие разборы
🚬 Вопросы, обучение, консультации: Консультации
@dima_sqlit
Условие: есть таблица сотрудников — отдел, имя, зарплата. Найди вторую по величине зарплату в каждом отделе.
Все понимают, что тут нужна оконная функция.
Но какую именно взять: на одинаковых зарплатах три похожие функции дают три разных ответа.
1️⃣ Данные
dept name salary
продажи Аня 150
продажи Боря 150
продажи Вика 120
продажи Гоша 100
IT Дима 200
IT Ева 180
IT Женя 160
Зарплаты в тысячах. У Ани и Бори одинаково, по 150 — вся задача про них.
2️⃣ Сначала уточни у интервьюера
«Вторая по величине» — это второй человек или второе значение зарплаты? В продажах второй человек получает 150, а второе значение — 120.
Обычно имеют в виду значение. Значит, правильный ответ:
продажи 120
IT 180
Задать этот вопрос вслух — уже плюс на собесе.
3️⃣ Что делает оконная функция
GROUP BY схлопывает отдел в одну строку. Оконная функция оставляет все 7 строк и дописывает рядом номер внутри отдела.
WITH r AS (
SELECT *,
ФУНКЦИЯ() OVER (
PARTITION BY dept
ORDER BY salary DESC
) AS n
FROM employees
)
SELECT dept, name, salary
FROM r WHERE n = 2;
PARTITION BY — нумеруем отдельно в каждом отделе. ORDER BY salary DESC — от большей зарплаты к меньшей. Фильтр по n стоит во внешнем запросе: оконные функции считаются после WHERE, поэтому в WHERE того же запроса n ещё не существует.
Осталось выбрать функцию.
4️⃣ ROW_NUMBER — мимо
Нумерует подряд, одинаковые или нет:
Аня 150 1
Боря 150 2 ← n = 2
Вика 120 3
Гоша 100 4
В продажах n = 2 у Бори, а у него 150 — это та же максимальная. Вдобавок кому из Ани и Бори достанется 1, база решает сама, и от запуска к запуску это может поменяться.
5️⃣ RANK — тоже мимо
Одинаковым даёт один номер, но следующий пропускает:
Аня 150 1
Боря 150 1
Вика 120 3 ← номер 2 пропущен
Гоша 100 4
Номера 2 в продажах нет — отдел просто пропал из ответа.
6️⃣ DENSE_RANK — то, что нужно
Одинаковым даёт один номер и следующий не пропускает:
Аня 150 1
Боря 150 1
Вика 120 2 ← n = 2
Гоша 100 3
Продажи — 120, IT — 180. Ровно то, что искали.
7️⃣ Финальный запрос
WITH r AS (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY dept
ORDER BY salary DESC
) AS n
FROM employees
)
SELECT dept, name, salary
FROM r WHERE n = 2;
-- продажи 120, IT 180
Итог: 🤩
На одинаковых зарплатах ROW_NUMBER берёт ту же максимальную, RANK теряет отдел, DENSE_RANK даёт правильный ответ.
❤️ Поддержать канал бустами, чтобы у автора появился дополнительный функционал можно - здесь (это бесплатно и доступно с подпиской telegram premium
✔️ Подпишитесь на канал, чтобы не пропустить следующие разборы
🚬 Вопросы, обучение, консультации: Консультации
@dima_sqlit