Взламываем SQL интервью на позиции в Тинькофф и Альфа-Банк
Давайте разберём задачи по SQL, которые могут встретиться на интервью у таких крупных компаний, как Тинькофф и Альфа-Банк.
Хитрые теоретические вопросы по SQL от Тинькофф
1) Может ли измениться результат запроса, если в LEFT JOIN поменять местами таблицы?
Да, если поменять местами таблицы в LEFT JOIN, результат запроса кардинально изменится. Все потому, что LEFT JOIN берет все строки из «левой» таблицы, дополняя их данными из «правой». Смена мест изменяет логику: теперь «правая» становится «левой» и наоборот. Это влияет на то, какие строки и как будут включены в результат.
2) 5 + NULL — это сколько?
В SQL, когда вы выполняете арифметическую операцию с NULL, результатом всегда будет NULL. Это связано с тем, что NULL представляет собой неопределенное значение, и любая операция с неопределенным значением также является неопределенной. Таким образом, 5 + NULL будет равно NULL.
3) Можно ли делать JOIN таблицы саму на себя?
Абсолютно! Это называется self-join и полезно для работы с иерархическими данными или для сравнения строк в таблице. Вот пример запроса:
SELECT e.EmployeeName AS Employee, m.EmployeeName AS Manager
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeIDВ этом примере таблица Employees соединяется сама с собой для того, чтобы получить имя сотрудника (Employee) и его непосредственного руководителя (Manager). Таблице Employees присваиваются два разных алиаса: e для сотрудников и m для руководителей. Условие соединения e.ManagerID = m.EmployeeID позволяет соединить сотрудников с их руководителями по идентификаторам
4) Какой из операторов SELECT, FROM, WHERE, GROUP BY выполняется последним? Тут главный герой — SELECT! Хотя он стоит на первом месте по синтаксису, логически данные выбираются после всех фильтраций и группировок. Так что, порядок такой: 1. FROM → 2. WHERE → 3. GROUP BY → 4. SELECT
5) Минимальное и максимальное количество записей в FULL JOIN таблицы на 10 и 100 строк?
С FULL JOIN всё работает так:
Минимум — 100 строк, если каждая из 10 нашла свою пару.
Максимум — 110 строк, если совпадений ноль и каждая строка появляется отдельно.
Задача с собеседования в Альфа-Банк №1:
Вывести имена покупателей, каждый из которых приобрёл Laptop и Monitor (использовать наименование товара product.name) в марте 2024 года.
Схема БД
Относительно несложная задача. Попробуйте решить её самостоятельно в онлайн тренажёре.
Ход решения
Шаг 1. Для начала давайте соединим 3 таблицы:
— Customer (клиенты) с Purchase (покупки) по customer_key,
— Purchase с Product (продукты) по product_key.
SELECT Customer.name
FROM Customer
JOIN Purchase ON Customer.customer_key = Purchase.customer_key
JOIN Product ON Purchase.product_key = Product.product_key
...Шаг 2. Далее нам необходимо отфильтровать записи в соответствии с условиями по наименованию товара и дате.
Добавим следующие фильтры в оператор WHERE:
— Product.name IN ("Laptop", "Monitor"),
— MONTH(Purchase.date) = 3 AND YEAR(Purchase.date) = 2024.
SELECT Customer.name
FROM Customer
JOIN Purchase ON Customer.customer_key = Purchase.customer_key
JOIN Product ON Purchase.product_key = Product.product_key
WHERE Product.name IN ('Laptop', 'Monitor')
AND MONTH(Purchase.date) = 3
AND YEAR(Purchase.date) = 2024
...Шаг 3. Теперь с помощью GROUP BY Customer.customer_key cгруппируем данные по каждому клиенту, чтобы можно было агрегировать информацию о покупках на уровне отдельного клиента.
Шаг 4. И, наконец, с помощью HAVING COUNT(DISTINCT Product.name) = 2 отфильтруем уже сгруппированные данные и оставим только те группы клиентов, которые совершили покупку обоих товаров — Laptop и Monitor.
Итоговое решение:
SELECT Customer.name
FROM Customer
JOIN Purchase ON Customer.customer_key = Purchase.customer_key
JOIN Product ON Purchase.product_key = Product.product_key
WHERE Product.name IN ('Laptop', 'Monitor')
AND MONTH(Purchase.date) = 3
AND YEAR(Purchase.date) = 2024
GROUP BY Customer.customer_key
HAVING COUNT(DISTINCT Product.name) = 2Задача с собеседования в Альфа-Банк №2:
Найдите средний возраст клиентов, приобретавших Smartwatch в 2024 году.
Схема БД
Задача среднего уровня сложности, чуть посложнее предыдущей. Её вы можете также попробовать решить самостоятельно в онлайн тренажёре.
Ход решения:
Шаг 1. Для начала необходимо соединить все необходимые таблицы:
— Customer и Purchase по customer_key,
— Purchase и Product по ключу product_key.
Шаг 2. Далее, необходимо отфильтровать данные в соответствии с условием задачи:
— по названию продукта: Product.name = "Smartwatch",
— по году покупки: YEAR(Purchase.date) = 2024.
На данном шаге мы имеем следующий запрос:
SELECT ...
FROM Purchase
JOIN Customer ON Purchase.customer_key = Customer.customer_key
JOIN Product ON Purchase.product_key = Product.product_key
WHERE Product.name = 'Smartwatch'
AND YEAR(Purchase.date) = 2024Шаг 3. Для того чтобы учесть каждого клиента только один раз, вне зависимости от количества покупок, совершённых им, необходимо получить уникальные записи с идентификаторами клиентов и их возрастами.
SELECT DISTINCT Customer.customer_key,
Customer.age
FROM Purchase
JOIN Customer ON Purchase.customer_key = Customer.customer_key
JOIN Product ON Purchase.product_key = Product.product_key
WHERE Product.name = 'Smartwatch'
AND YEAR(Purchase.date) = 2024Шаг 4. Теперь, когда мы имеем список уникальных пользователей и их возрастов, мы можем произвести расчет среднего возраста клиентов. Для этого используем функцию AVG().
Итого, мы получаем следующий запрос:
SELECT AVG(uniqueCustomers.age) AS average_age
FROM (
SELECT DISTINCT Customer.customer_key, Customer.age
FROM Purchase
JOIN Customer ON Purchase.customer_key = Customer.customer_key
JOIN Product ON Purchase.product_key = Product.product_key
WHERE Product.name = 'Smartwatch'
AND YEAR(Purchase.date) = 2024
) AS uniqueCustomersКроме того, на сайте SQL Academy есть задачи с собеседований других крупных компаний. Заходите, решайте, и удачи на собеседованиях!
Также мы активно ведём наш телеграм-канал с полезной информацией по SQL.
