Найдите вторую по величине зарплату в каждом отделе, если у нескольких сотрудников она одинаковая

ЗадачаMiddleИногда

Проверила команда СопроводОбновлено

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

Пронумеруйте зарплаты внутри каждой клиники через dense_rank() OVER (PARTITION BY clinic ORDER BY salary DESC) и во внешнем запросе оставьте строки с рангом 2. DENSE_RANK дает равным зарплатам один ранг и не пропускает номера, поэтому ранг 2 — это вторая по величине сумма. Добавьте DISTINCT: вторую зарплату могут получать несколько врачей.

Почему этот вопрос задают

Классическая задача на оконные функции с подвохом в данных. По ней видно, различаете ли вы ROW_NUMBER, RANK и DENSE_RANK и знаете ли, на каком шаге запроса считается окно.

Условие задачи

Сеть ветеринарных клиник хранит врачей в таблице vets(id, clinic, name, salary), отдел здесь — клиника. Для обзора зарплат нужна вторая по величине зарплата в каждой клинике. В «Северной» и «Южной» максимум получают по два врача, в «Центральной» двое делят второе место. Выведите клинику и вторую зарплату, по одной строке на клинику, в порядке названий клиник.

idclinicnamesalary
1СевернаяОрлова90000
2СевернаяКим90000
3СевернаяЛебедев75000
4СевернаяЗуева60000
5ЮжнаяПетров85000
6ЮжнаяНосова85000
7ЮжнаяГусев70000
8ЦентральнаяМаркова95000
9ЦентральнаяСоколов80000
10ЦентральнаяЯшин80000

Как подойти к решению

  1. Договоритесь, что значит «вторая»: вторая по величине сумма без учета повторов
  2. Посчитайте dense_rank() по убыванию зарплаты внутри каждой клиники
  3. Вынесите окно в подзапрос и фильтруйте ранг во внешнем запросе
  4. Уберите повторы через DISTINCT, если вторую зарплату делят несколько врачей

Решение

Сортировка окна по убыванию делает максимум рангом 1. В «Северной» ранги идут 1, 1, 2, 3, и ранг 2 достается 75000. В «Центральной» ранг 2 у двух врачей, и DISTINCT сворачивает их в одну строку. Клиника, где у всех одна зарплата, в ответ не попадет: ранга 2 там нет.

SQL
SELECT DISTINCT clinic, salary AS second_salary
FROM (
  SELECT clinic, salary,
         dense_rank() OVER (PARTITION BY clinic ORDER BY salary DESC) AS rnk
  FROM vets
) AS ranked
WHERE rnk = 2
ORDER BY clinic;
Вывод
   clinic    | second_salary
-------------+---------------
 Северная    |         75000
 Центральная |         80000
 Южная       |         70000
(3 rows)

Что спросят следом

  • Как решить задачу без оконных функций?

  • Как показать клинику с NULL, если второй зарплаты там нет?

  • А если нужны имена врачей со второй зарплатой?

Доступно по подписке

Ответы на эти вопросы

Частые ошибки

  • «Возьму ROW_NUMBER и строку номер 2»

    ROW_NUMBER нумерует строки подряд, и при двух одинаковых максимумах вторая строка снова даст максимум. В «Северной» вы получите 90000 вместо 75000

  • «RANK подойдет, он учитывает равенство»

    RANK после двух первых мест ставит сразу 3, и ранга 2 в «Северной» нет вовсе. Без пропусков номера ставит только DENSE_RANK

  • «Отфильтрую прямо в WHERE dense_rank() OVER (...) = 2»

    Оконные функции считаются после WHERE, и PostgreSQL выдаст ошибку. Посчитайте ранг в подзапросе или CTE и фильтруйте снаружи

  • «Отсортирую окно по возрастанию и возьму предпоследний ранг»

    Тогда придется знать число рангов в каждой клинике, и запрос усложнится. Сортируйте по убыванию: вторая сверху зарплата всегда получит ранг 2

Сигналы для интервьюера

Сильный ответ

Без подсказки обобщает запрос на N-ю зарплату: меняет одну цифру в условии и объясняет, почему этого хватает

Тревожный сигнал

Решает через SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET 1, что годится только для одной клиники, и не замечает, что их несколько

Красный флаг

Считает, что при одинаковых зарплатах у задачи нет ответа, и предлагает объявить такие строки ошибкой данных

Как еще могут спросить

  • Как найти N-ю по величине зарплату в каждой группе?
  • Напишите запрос, который вернет вторую максимальную зарплату по отделам
  • Как с помощью DENSE_RANK найти вторую зарплату, если есть одинаковые значения?
  • Как получить второе по величине значение в каждой группе с учетом повторов?

Похожие вопросы

ЗнаниеJuniorЧасто

Чем ROW_NUMBER отличается от RANK и DENSE_RANK?

Все три функции нумеруют строки окна в порядке из ORDER BY, а различаются на равных значениях. ROW_NUMBER дает каждой строке свой номер, даже при равенстве. RANK дает равным строкам одинаковый номер и пропускает следующие: 1, 2, 2, 4. DENSE_RANK тоже дает одинаковый номер, но без пропусков: 1, 2, 2, 3.

  • оконные функции
239
ЗнаниеJuniorЧасто

Чем оконная функция отличается от GROUP BY?

GROUP BY схлопывает строки: на выходе одна строка на группу, и в SELECT остаются только столбцы группировки и агрегаты. Оконная функция с OVER считает агрегат по группе строк, но каждая строка остается в результате со всеми столбцами. Поэтому окно берут, когда рядом с каждой строкой нужно число по ее группе: среднее, ранг, итог.

  • оконные функции
  • GROUP BY
274
ЗнаниеJuniorЧасто

В каком порядке выполняются части запроса SELECT и почему в WHERE нельзя сослаться на псевдоним из SELECT?

Логически запрос выполняется так: FROM и JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Псевдоним появляется только на шаге SELECT, а WHERE работает раньше, когда такого столбца еще нет. Поэтому повторите выражение целиком или вынесите расчет в подзапрос и фильтруйте снаружи. ORDER BY идет после SELECT и псевдоним видит.

  • SELECT
  • псевдонимы
227
ЗадачаSeniorРедко

Посчитайте медиану чека по каждому городу

Посчитайте медиану упорядоченным агрегатом: percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) с GROUP BY city. При четном числе заказов он берет среднее двух центральных значений, percentile_disc(0.5) вернет меньшее из них, то есть реальный чек. Пустые суммы оба пропускают. avg медианой не считается: один крупный заказ сдвигает среднее, медиана почти не меняется.

  • медиана
  • percentile_cont
103

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Третью или N-ю зарплату, трех лучших сотрудников каждого отдела, самый дорогой товар в категории. Все они решаются одним приемом: ранжирующая функция в подзапросе и фильтр по рангу снаружи. Разберитесь, где нужен ROW_NUMBER, а где DENSE_RANK, и дальше это шаблон.

Результат одинаковый, выбирайте то, что вам проще читать. С PostgreSQL 12 простой CTE встраивается в запрос так же, как подзапрос, и на план не влияет. На собеседовании CTE с понятным именем вроде ranked помогает проговорить решение по шагам.

Спросите, бывает ли зарплата пустой и нужна ли строка для клиники, где второй зарплаты нет. Пустая зарплата ломает ответ: при сортировке по убыванию NULL встает первым и забирает ранг 1, тогда ранг 2 получит максимум. Отсейте пустые зарплаты в подзапросе через WHERE salary IS NOT NULL.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

Назовите одно достижение, близкое к задачам новой работы, и расскажите его как историю: какая была проблема, что сделали лично вы и что изменилось в цифрах. Сильнее всего звучит результат, который держится и после вас: новый порядок работы, правило, схема. Если результат общий, прямо скажите, какая часть в нем ваша.

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

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

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

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

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

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

  • цели
  • планирование
227