Объяснение подхода к решению нескольких сложных SQL-запросов.

SQL (Structured Query Language) – очень важный инструмент в арсенале специалиста по исследованию данных. Владение SQL необходимо не только для прохождения собеседования, но и для того, чтобы уметь решать сложные запросы и быть крутым специалистом.

В этой статье я расскажу о 5 каверзных вопросах SQL, которые мне попадались, и о своих подходах к их решению.

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

Запрос 1

Нам дана таблица, состоящая из двух столбцов: Name и Profession. Нам необходимо запросить все имена, за которыми сразу следует первая буква в столбце “Profession“, заключенная в круглые скобки.

Мое решение

SELECT
CONCAT(Name, ’(‘, SUBSTR(Profession, 1, 1), ’)’) 
FROM table;

Так как нам необходимо объединить имя и профессию, мы можем использовать переменную CONCAT. Также нам необходимо, чтобы внутри круглой скобки была только одна буква. Поэтому мы будем использовать SUBSTR и передавать имя столбца, начальный индекс, конечный индекс. Поскольку нам нужна только первая буква, передадим 1,1 (начальный индекс включительно, а конечный – не включительно)

Запрос 2

Тину попросили вычислить среднюю зарплату всех сотрудников из созданной ею таблицы EMPLOYEES, но она поняла, что клавиша “ноль” на ее клавиатуре не работает, после чего результат показал очень маленькое среднее значение. Она просит помочь ей найти разницу между ошибочно рассчитанной средней и фактической.

Необходимо написать запрос, находящий ошибку( Фактическое AVG – Рассчитанное AVG).

Мое решение

SELECT  
AVG(Salary) - AVG(REPLACE(Salary, 0, ’’))  
FROM table;

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

Запрос 3

Нам дана таблица, представляющая собой дерево бинарного поиска, состоящее из двух столбцов Node и Parent. Необходимо написать запрос, который возвращает тип узла, упорядоченный по значению узлов в порядке возрастания. Существует 3 типа.

Корень – если узел является корнем
Leaf – если узел является листом
Inner – если узел не является ни корнем, ни листом.

Мое решение

При первичном анализе можно сделать вывод, что если для данного узла N соответствующее ему значение P равно NULL, то он является корнем. А для данного узла N, если он существует в графе P, то он не является внутренним узлом. Исходя из этого, составим запрос.

SELECT CASE
    WHEN P IS NULL THEN CONCAT(N, ' Root')
    WHEN N IN (SELECT DISTINCT P from BST) THEN CONCAT(N, ' Inner')
    ELSE CONCAT(N, ' Leaf')
    END
FROM BST
ORDER BY N asc;

Мы можем использовать CASE, который действует как переключающая функция. Как я уже говорил, если для данного узла N значение P равно null, то N является корнем. Поэтому мы использовали функциюCONCAT для объединения значения и метки узла. Аналогично, если данный узел N находится в столбце P, то он является внутренним узлом. Чтобы получить все узлы из столбца P, мы написали подзапрос, который возвращает все отдельные узлы в столбце P. Поскольку нам было предложено упорядочить вывод по возрастанию значений узлов, мы использовали предложение ORDER BY.

Запрос 4

Нам дана таблица транзакций, состоящая из transaction_id, user_id, transaction_date, product_id и quantity. Нам необходимо запросить количество пользователей, которые приобрели товары в течение нескольких дней (обратите внимание, что один пользователь может приобрести несколько товаров в один день).

Мое решение

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

SELECT COUNT(user_id)
FROM
(
SELECT user_id
 FROM orders
 GROUP BY user_id
 HAVING COUNT(DISTINCT DATE(date)) > 1
) t1

Поскольку в вопросе запрашивается количество user_ids, а не сами user_id, во внешнем запросе мы используем COUNT.

Запрос 5

Нам дана таблица подписки, которая состоит из даты начала и окончания подписки для каждого пользователя. Нам необходимо написать запрос, который возвращает истину/ложь для каждого пользователя на основе совпадения дат с другими пользователями. Например, если период подписки пользователя user1 совпадает с периодом подписки любого другого пользователя, то запрос должен возвращать True для user1.

Мое решение

При первичном анализе мы понимаем, что нам необходимо сравнить каждую подписку с каждой другой. Рассмотрим даты начала и окончания подписки пользователяА как startA и endA, аналогично для пользователяВ,startB и endB.

Если startA≤endB и endA≥startB, то можно сказать, что два диапазона дат пересекаются. Рассмотрим два примера. Сначала сравним U1 и U3.

startA = 2020–01–01
endA = 2020–01–31
startB = 2020–01–16
endB = 2020–01–26

Здесь мы видим, что startA(2020-01-01) меньше endB(2020-01-26) и аналогично endA(2020-01-31) больше startB(2020-01-16), из чего можно сделать вывод, что даты пересекаются. Аналогично, если сравнить U1 и U4, то приведенное выше условие не сработает и вернет false.

Мы также должны убедиться, что пользователь не сравнивается со своей собственной подпиской. Мы также хотим выполнить левое соединение на самом себе, чтобы сопоставить пользователя с каждым другим пользователем, удовлетворяющим нашему условию. Теперь создадим две реплики s1 и s2 одной и той же таблицы.

SELECT *
FROM subscriptions AS s1
LEFT JOIN subscriptions AS s2
    ON s1.user_id != s2.user_id
        AND s1.start_date <= s2.end_date
        AND s1.end_date >= s2.start_date

При условном объединении для каждого user_id из s2 должен существовать user_id из s1 при условии совпадения дат.

Вывод

Мы видим, что для каждого пользователя существует еще один пользователь, если даты совпадают. Для пользователя 1 имеется 2 строки, что указывает на то, что он совпадает с двумя пользователями. Для пользователя 4 соответствующий идентификатор равен null, что означает, что он не совпадает ни с одним другим пользователем.

Теперь мы можем сгруппировать все по полю s1.user_id и просто проверить, существует ли значение true для пользователя, где s2.user_id IS NOT NULL.

Итоговый запрос

SELECT
    s1.user_id
    , (CASE WHEN s2.user_id IS NOT NULL THEN 1 ELSE 0 END) AS overlap
FROM subscriptions AS s1
LEFT JOIN subscriptions AS s2
    ON s1.user_id != s2.user_id
        AND s1.start_date <= s2.end_date
        AND s1.end_date >= s2.start_date
GROUP BY s1.user_id

Мы использовали предложение CASE для обозначения 1 и 0 в зависимости от значения s2.user_id для данного пользователя. Итоговый результат выглядит следующим образом –

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

SQL Cookbook: Query Solutions and Techniques for Database Developers (Cookbooks (O’Reilly))

Заключение

Освоение SQL требует много практики. В этой статье я разобрал 5 каверзных вопросов и объяснил подходы к их решению. Особенность SQL заключается в том, что каждый запрос может быть написан множеством различных способов. Не стесняйтесь делиться своими подходами в ответах. Надеюсь, сегодня вы узнали что-то новое!