Перевод статьи «SQL Interview Questions».
Ниже примеры двух тестовых заданий, которые мне попались на техническом собеседовании. Несмотря на то, что в сети полно похожих постов на тему «Как решать задачи с Leetcode», я всё-таки добавлю свои пять копеек — чем больше примеров, тем легче готовиться.
🔥 Важное для QA-специалистов! 🔥
В QaRocks ты найдешь туториалы, задачи и полезные книги, которых нет в открытом доступе. Уже более 15.000 подписчиков – будь среди нас! Заходи к нам в телеграм канал QaRocks
Сложность заключалась в том, что задачи нужно было решить без компилятора — приходилось мысленно представлять поведение кода. А с этим у меня, честно говоря, не очень 😀 Вот сами задачи:
1. Есть таблица с колонками Id и email. Необходимо вывести дублирующиеся адреса электронной почты:
Нужны именно дубли! Не уникальные записи, а именно те, что повторяются!
Первое, что пришло в голову:
SELECT NOT DISTINCT (id) FROM clients
Разумеется, это не сработало, так как функции NOT DISTINCT() просто не существует, и технически невозможно “отрицать” функцию 🙂
Тогда я попробовала сделать так:
SELECT email FROM clients WHERE COUNT(id) != 1 GROUP BY email
Читайте также: Самые популярные вопросы на собеседованиях по SQL
Теплее, но не совсем то!
Сначала я даже подумала, что ошибка связана с условием COUNT(id) != 1, но дело оказалось не в этом. Проблема заключалась в том, что агрегатные функции нельзя использовать в WHERE — вместо них нужен HAVING().
Важно помнить, что HAVING всегда идёт после GROUP BY, а WHERE — до:
SELECT email FROM clients GROUP BY email HAVING COUNT(id) != 1
Позже мне пришло более хитрое решение. Поскольку задача связана с дубликатами, почему бы не выполнить self join — соединить таблицу саму с собой по полю id?
Если мы выполним следующий запрос:
SELECT a.id, a.email FROM clients a JOIN clients b ON a.email = b.email
то мы получим следующее:
Только одна единственная строка с уникальным email (строка 5) не будет соединена ни с одной другой. Но нам как раз нужно отфильтровать такие уникальные адреса и оставить только те, что повторяются — то есть найти совпадающие email’ы, но с разными id. Например, строки 1 и 6.
Значит, надо добавить условие: id из первой таблицы не должен совпадать с id из второй.
---- JOIN clients b ON a.email = b.email WHERE a.id <> b.id;
Главное помнить, что нам нужен только столбец email:
И, конечно же, не забыть про DISTINCT, чтобы выкинуть дубли:
SELECT a.email FROM clients a JOIN clients b ON a.email = b.email WHERE a.id <> b.id;
Попробуйте выполнить этот запрос — должно сработать:
2. Есть таблица с полями id | name | salary. Необходимо найти id сотрудника, у которого вторая по величине зарплата.
Итак, надо, чтобы запрос вернул id Ады (5) или Элайджи (3). Стоит отметить, что в условии задачи не сказано, что делать в случае с одинаковыми зарплатами, поэтому мы оставляем это на наше усмотрение.
Изначально я предполагала отсортировать сотрудников по зарплате:
SELECT id, name, MAX(salary) FROM employees GROUP BY id, name ORDER BY MAX(salary) DESC
что привело бы к следующему результату:
и затем просто извлечь вторую строку.
Один из способов — присвоить зарплатам порядковые номера по убыванию значений, вычесть единицу из нужного порядкового номера и получить id, соответствующий этой позиции. Это вариант с использованием оконных функций (что не является моей сильной стороной 😀 )
… или можно воспользоваться OFFSET! Вот такой запрос как раз делает то, что надо — просто сдвигается на одну строку:
SELECT id, name, MAX(salary) FROM employees GROUP BY id, name ORDER BY MAX(salary) DESC OFFSET 1 ROWS
— этот запрос просто пропустит первую строку и начнёт со второй:
А чтобы получить только вторую строку, необходимо указать, что нам нужна всего одна строка:
SELECT id, name, MAX(salary) FROM employees GROUP BY id, name ORDER BY MAX(salary) DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
И в результате запрос вернёт только одну строку:
Но здесь возникает вопрос: как система определит, кого из сотрудников вернуть — Аду или Элайджу? 😀
Альтернативный способ решения задачи — использовать конструкцию LIMIT. Если после сортировки ограничить выборку одной строкой (LIMIT 1), то мы получим максимальную зарплату. Но так как нам нужна вторая по величине, используем LIMIT 2:
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2
И в результате получим:
Почему бы не воспользоваться в данном случае функцией MIN? Если взять минимум из двух наибольших зарплат — как раз получится вторая по величине:
SELECT MIN(salary) AS second_max_salary
FROM (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 2
) AS top_salaries;
Третий способ — использовать вместе LIMIT и OFFSET:
SELECT salary FROM employees LIMIT 1 OFFSET 2
Это довольно просто и понятно.
Четвёртый, заключительный способ, основан на работе с множествами — как в математике.
Допустим, MAX(salary) — это множество, состоящее из одного значения. Это множество является подмножеством всех зарплат. Остальными элементами этого множества являются все другие зарплаты:
Если перевести вышесказанное на язык SQL, получаем вот такой запрос:
SELECT MAX(salary)
FROM employees
WHERE salary NOT IN (SELECT MAX(salary)
FROM employees)