Классика: вторая по величине зарплата

«Найди вторую по величине зарплату» это, пожалуй, самая известная SQL-задача собеседований. Она короткая, но проверяет сразу несколько вещей: понимаешь ли ты повторы, NULL, подзапросы и оконные функции. Интервьюеру важен не один правильный запрос, а то, видишь ли ты ловушки. В учебной базе блока главная ловушка уже заложена: Борис и Вера получают одинаковые 250 000.

Как спрашивают

Напиши запрос, который вернёт вторую по величине зарплату в компании. Что вернёт твой запрос, если второй зарплаты нет? А как найти N-ю?

Короткий ответ

«Вторая по величине» означает второе различное значение, а не вторую строку. Самый переносимый ответ: SELECT max(salary) FROM employees WHERE salary < (SELECT max(salary) FROM employees). Он сам вернёт NULL, если второй зарплаты нет. Для N-й зарплаты берут DENSE_RANK(): он не пропускает места при равных значениях. Обязательно скажи вслух, как твой запрос ведёт себя при одинаковых зарплатах и при отсутствии ответа.

Разбор: второе место на конкурсе

Представь конкурс, где жюри выставило баллы: 300, 250, 250, 200 и так далее. Вопрос «какой второй по величине балл» и вопрос «кто стоит вторым в списке» разные. Второй по величине балл 250, хотя его набрали двое. А вторым в списке стоит кто-то из этих двоих, кого записали раньше. Почти все ошибки в этой задаче от путаницы этих двух вопросов.

Ответ для нашей базы: 250 000.

Способ 1: максимум среди тех, кто меньше максимума

SELECT max(salary) AS second
FROM employees
WHERE salary < (SELECT max(salary) FROM employees);

Логика: убери всех, у кого максимальная зарплата, и найди максимум среди оставшихся. Работает в любой СУБД, повторы не мешают. Если второй зарплаты нет, max по пустому набору даёт NULL, а не пустой результат. Обычно просят NULL. Минус: на N-ю зарплату не обобщается без матрёшки из подзапросов.

Способ 2: сортировка с пропуском

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

OFFSET пропускает первую строку, LIMIT 1 берёт следующую. Для N-й зарплаты пиши OFFSET N - 1.

Здесь две тонкости:

• Без DISTINCT ответ ломается. Для второй зарплаты он случайно верен: OFFSET 1 попадает на Бориса с 250 000. А для третьей OFFSET 2 попадёт на Веру и тоже вернёт 250 000, хотя третья различная зарплата 200 000.
• Пустой результат вместо NULL. Если второй зарплаты нет, запрос вернёт ноль строк. Если интервьюер просит NULL, оберни запрос в скалярный подзапрос: SELECT (SELECT DISTINCT salary ... LIMIT 1 OFFSET 1) AS second. Пустой скалярный подзапрос даёт NULL.

Способ 3: DENSE_RANK

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 2;

DENSE_RANK даёт Борису и Вере одно место 2, а Глебу место 3 без пропуска, как в уроке про ранжирование. Поэтому r = N всегда означает N-е различное значение. Это лучший ответ на вопрос «а N-ю?».

Почему не RANK? У него после двух вторых идёт сразу четвёртое место, и r = 3 не найдёт ничего. Почему не ROW_NUMBER? Он даст Вере номер 3, и третьей зарплатой окажутся её же 250 000.

Если нужны ещё и имена, убери DISTINCT и выведи name:

name  | salary
------+-------
Борис | 250000
Вера  | 250000

Способ 4: коррелированный подзапрос

SELECT DISTINCT e.salary
FROM employees e
WHERE 1 = (
  SELECT count(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary > e.salary
);

Смысл: вторая по величине зарплата это та, выше которой ровно одно различное значение. Для N-й поставь N - 1 вместо 1. Это старый способ, он работает там, где нет оконных функций, но логически считает подзапрос для каждой строки. На собеседовании его полезно знать как ответ на «а без оконных функций?».