10 задач для собеседования по SQL-запросам

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

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

БЕСПЛАТНО СКАЧАТЬ КНИГИ в телеграм канале "Библиотека тестировщика"

1. Рассчитайте процентный вклад каждого магазина в общий объём продаж

SELECT stor_id, SUM(qty) AS total_qty, (SUM(qty) / (SELECT SUM(qty) FROM sales) * 100) AS percentage
FROM sales
GROUP BY stor_id
ORDER BY SUM(qty) DESC;

2. Найдите второй по стоимости заказ в таблице

Эту задачу можно решить с использованием операторов LIMIT и OFFSET или с помощью подзапроса.

Обратите внимание:

• LIMIT обозначает число строк, которые будут возвращены в наборе результатов
• OFFSET обозначает количество строк, которые будут пропущены в наборе результатов

-- Использование LIMIT OFFSET

SELECT order_id, price
FROM order_items
ORDER BY price DESC
LIMIT 1 OFFSET 1; 


-- Использование подзапроса

SELECT MAX(price)
FROM order_items
WHERE price < (SELECT MAX(price) FROM order_items);

-- Найти вторую по величине зарплату в таблице

 SELECT MAX(salary) AS SecondHighestSalary
 FROM Employee
 WHERE salary < (SELECT MAX(salary) FROM Employee);

3. Рассчитайте объём продаж для каждого магазина, учитывая только заказы за 1993 год

-- OPTION 1

SELECT stor_id, SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS total_sales
FROM sales
GROUP BY stor_id;

-- OPTION 2

SELECT stor_id, SUM(qty)
FROM sales
WHERE YEAR(ord_date) = 1993
GROUP BY stor_id;

4. Рассчитайте объём экспорта и импорта для каждой компании

Обратите внимание: экспорт — это когда компания является продавцом, а импорт — когда компания является покупателем.

SELECT country,
SUM(CASE WHEN companies.name = trades.seller THEN trades.value ELSE 0 END) AS export,
SUM(CASE WHEN companies.name = trades.buyer THEN trades.value ELSE 0 END) AS import
FROM companies
LEFT JOIN trades ON companies.name = trades.seller OR companies.name = trades.buyer
GROUP BY companies.country
ORDER BY companies.country ASC;

5. Определите месяц с наибольшим числом заказов в прошлом году

Обратите внимание: DATE_ADD() и DATE_SUB() выполняют операции сложения или вычитания со значениями даты. Например, DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH) демонстрирует 3 месяца до текущей даты.

SELECT MONTH(ord_date), SUM(qty)
FROM sales
WHERE ord_date >= DATE_SUB(ord_date, INTERVAL 1 YEAR)
GROUP BY MONTH(ord_date)
ORDER BY SUM(qty) DESC
LIMIT 1;

6. Найдите книги, которые не были выпущены в последние шесть месяцев

SELECT title, MONTH(pubdate), pubdate
FROM titles
WHERE pubdate IN (
    SELECT pubdate
    FROM titles
    WHERE pubdate < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
);

7. Рассчитайте накопленную сумму в столбце «количество» в таблице

SELECT qty, SUM(qty) OVER (ORDER BY qty) AS cumulative_sum
FROM sales;

8. Получите дату последнего заказа для каждого магазина

SELECT stor_id, MAX(ord_date) AS most_recent_order
FROM sales
GROUP BY stor_id
ORDER BY most_recent_order DESC;

9. Рассчитайте среднюю сумму заказа для каждого месяца в 1993 году

SELECT MONTH(ord_date) AS month, AVG(qty)
FROM sales
WHERE YEAR(ord_date) = 1993
GROUP BY month
ORDER BY AVG(qty) DESC;

10. Найдите магазины, которые продали как минимум 3 товара в мае 1993 года

SELECT stor_id, SUM(qty)
FROM sales
WHERE MONTH(ord_date) = '05' AND YEAR(ord_date) = 1993
GROUP BY stor_id
ORDER BY SUM(qty) DESC;

Перевод статьи «10 SQL Interview Coding Questions for Data Analysts and Data Scientists».