Как найти заказ, занимающий второе место по сумме в таблице заказов?

Что проверяют

Знаете ли вы несколько способов решения и видите ли подвох с одинаковыми суммами. У тестировщика так проверяют, умеет ли он сам проверить данные в базе, а не только читать отчёт. Следом почти всегда звучит: «А если два заказа с максимальной суммой?»

Как отвечать

Сначала уточните, что значит «второе место»: вторая строка при сортировке или вторая по величине сумма. От этого зависит запрос.

• ORDER BY и LIMIT: SELECT * FROM orders ORDER BY amount DESC LIMIT 1 OFFSET 1. Просто, но при двух заказах с одинаковой максимальной суммой вернёт второй из них, то есть ту же сумму;
• подзапрос с MAX: SELECT MAX(amount) FROM orders WHERE amount < (SELECT MAX(amount) FROM orders). Даёт именно вторую по величине сумму и работает в любой СУБД, но возвращает число, а не заказ, сам заказ достаётся ещё одним условием;
• DENSE_RANK(): DENSE_RANK() OVER (ORDER BY amount DESC) в подзапросе и отбор WHERE rnk = 2. Одинаковые суммы получают один ранг, следующая сумма идёт вторым рангом без пропуска, и в ответ попадают все заказы с этой суммой;
• RANK() и ROW_NUMBER(): RANK() после двух одинаковых первых мест сразу даёт 3, ранга 2 не будет вовсе, а ROW_NUMBER() нумерует подряд и ведёт себя как OFFSET.

Добавьте про NULL: в PostgreSQL при DESC пустые значения по умолчанию идут первыми, и без NULLS LAST или WHERE amount IS NOT NULL на первом месте окажется заказ без суммы.

Закончите тем, как вы проверите сам запрос: таблица из одного заказа, два заказа с равной максимальной суммой, все суммы одинаковы. Ожидаемый результат на каждом наборе назовите заранее.

Где спотыкаются

Пишут LIMIT 1 OFFSET 1 и не думают про дубли. Интервьюер добавляет второй заказ на максимальную сумму, и ответ ломается.

Путают RANK() и DENSE_RANK() и не могут объяснить, куда пропал ранг 2.

Называют колонку rank в MySQL 8, где это зарезервированное слово, и получают синтаксическую ошибку. Безопаснее назвать её rnk.