Четыре частых вопроса по SQL джуну-аналитику и три задачи на собеседовании. Часть 1

Привет, Хабр!

Для аналитиков, владение SQL — это база. И от познаний в SQL зачастую зависит, отправит ли вам компания, где вы собеседуетесь — оффер.

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

Как оптимизировать SQL запросы?

Очень частый вопрос. Выделим три основных вида оптимизации.

Индексация

Индексация работает по принципу книжного указателя, позволяя БД быстро найти нужную строку без необходимости просмотра всех записей.

CREATE INDEX idx_customer_name ON customers (name);

Индекс поможет ускорить выполнение запросов, которые фильтруют или сортируют данные по столбцу name в таблице customers. Например, запрос:

SELECT * FROM customers WHERE name = 'Иван Иванов';

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

Партиционирование

Партиционирование позволяет разделить таблицу на меньшие, более управляемые части.

CREATE TABLE sales (   sale_id INT AUTO_INCREMENT,   sale_date DATE,   amount DECIMAL(10, 2),   PRIMARY KEY (sale_id) ) PARTITION BY RANGE (YEAR(sale_date)) (   PARTITION p0 VALUES LESS THAN (1991),   PARTITION p1 VALUES LESS THAN (1992),   PARTITION p2 VALUES LESS THAN (1993),   ... );

Таблица sales разделена на партиции по годам продаж. Это позволяет выполнить запросы, ограниченные определенным годом, намного быстрее, так как SQL-сервер сможет обратиться непосредственно к нужной партиции.

Нормализация

Нормализация — процесс организации данных в БД, т.е устранение избыточности и зависимостей.

Перед нормализацией:

CREATE TABLE orders (   order_id INT,   customer_name VARCHAR(100),   customer_address VARCHAR(255),   order_date DATE );

После нормализации:

CREATE TABLE customers (   customer_id INT AUTO_INCREMENT PRIMARY KEY,   name VARCHAR(100),   address VARCHAR(255) );  CREATE TABLE orders (   order_id INT AUTO_INCREMENT PRIMARY KEY,   customer_id INT,   order_date DATE,   FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Информация о клиенте вынесена в отдельную таблицу, что уменьшает дублирование данных и упрощает их обновление.

Про джоины

Работа с JOINs — это основа для многих сложных аналитических задач. JOINы позволяют объединять данные из разных таблиц для получения комплексных отчетов и анализа данных.

INNER JOIN

Допустим, нужно получить список всех заказов с информацией о клиентах.

SELECT orders.order_id, customers.name, orders.order_date FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_date > '2021-01-01';

Запрос объединяет таблицы orders и customers по customer_id для получения информации о заказах, сделанных после определенной даты.

LEFT JOIN

Нужно выявить клиентов, которые не делали заказов.

SELECT customers.name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_id IS NULL;

LEFT JOIN используется для объединения данных о клиентах с данными о заказах. В результат включаются все клиенты, но заказы присутствуют только у тех, кто что-то заказывал. Фильтр по orders.order_id IS NULL позволяет идентифицировать клиентов без заказов.

RIGHT JOIN

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

SELECT products.product_name, orders.order_id FROM products RIGHT JOIN orders ON products.product_id = orders.product_id;

RIGHT JOIN аналогичен LEFT JOIN, но в этом случае в результат попадут все заказы, и если какие-то товары не были в заказах, то соответствующие поля product_name будут заполнены как NULL.

FULL OUTER JOIN

Интегрируем данные о клиентах и заказах для полного отчета.

SELECT customers.name, orders.order_id FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN объединяет LEFT и RIGHT JOIN, включая все строки из обеих таблиц. Если совпадений нет, соответствующие поля заполняются NULLами.

CROSS JOIN

Нужно сгенерировать все возможные пары продуктов для анализа кросс-продаж.

SELECT A.product_name, B.product_name FROM products A CROSS JOIN products B WHERE A.product_id != B.product_id;

CROSS JOIN создает декартово произведение двух таблиц, что мастхев для анализа всех возможных комбинаций элементов из двух таблиц. В данном случае генерируются все возможные пары продуктов, за исключением пар с одинаковыми продуктами.

Оконные функции

Оконные функции позволяют выполнять сложные вычисления, такие как кумулятивные суммы, движущиеся средние и ранжирование, не агрегируя при этом строки в единую группу.

ROW_NUMBER() для уникальной нумерации строк

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

SELECT customer_id, order_id, order_date,        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS order_sequence FROM orders;

ROW_NUMBER() присваивает уникальный порядковый номер каждому заказу в пределах каждого клиента, упорядоченный по дате заказа.

RANK() и DENSE_RANK() для ранжирования данных

Ранжировать продажи сотрудников в рамках их отделов.

SELECT department_id, employee_id, sales_amount,        RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS sales_rank,        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS dense_sales_rank FROM sales_records;

RANK() присваивает ранг продажам сотрудников в каждом отделе, пропуская ранги при одинаковых значениях, в то время как DENSE_RANK() делает то же самое, но без пропусков рангов.

Кумулятивная сумма с использованием SUM()

Нужен расчет кумулятивной суммы продаж по дням.

SELECT order_date, daily_sales,        SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales FROM sales_by_date;

SUM() с оконной спецификацией OVER позволяет рассчитать кумулятивную сумму продаж, суммируя значения daily_sales начиная с начала набора данных до текущей строки.

Вычисление скользящего среднего

Вычисляем 7-дневное скользящее среднего объема продаж.

SELECT order_date, sales_amount,        AVG(sales_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_sales FROM sales_records; 

Юзаем AVG() с оконной функцией, заданной для расчета среднего значения sales_amount за текущий и предыдущие 6 дней.

Работа с подзапросами и CTE

Подзапросы и CTE улучшают структуру запросов.

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

Находим клиентов, которые тратят больше среднего по всем клиентам.

SELECT customer_id, total_spent FROM (     SELECT customer_id, SUM(amount) AS total_spent     FROM purchases     GROUP BY customer_id ) AS customer_spending WHERE total_spent > (     SELECT AVG(total_spent) FROM (         SELECT SUM(amount) AS total_spent         FROM purchases         GROUP BY customer_id     ) AS average_spending );

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

CTE для разделения сложных задач

Проведем анализ цепочки поставок для определения замедления доставки.

WITH SupplierData AS (     SELECT supplier_id, region, COUNT(*) AS num_orders     FROM orders     GROUP BY supplier_id, region ), RegionalDelays AS (     SELECT region, AVG(delay) AS avg_delay     FROM deliveries     GROUP BY region ) SELECT sd.supplier_id, sd.region, sd.num_orders, rd.avg_delay FROM SupplierData sd JOIN RegionalDelays rd ON sd.region = rd.region WHERE rd.avg_delay > 10;

В запросе два CTE SupplierData и RegionalDelays используются для сегментации данных по поставщикам и региональным задержкам. Эти выражения затем объединяются для анализа.

Рекурсивные CTE

Генерируем серии дат.

WITH RECURSIVE DateSeries AS (     SELECT '2021-01-01' AS date     UNION ALL     SELECT DATE_ADD(date, INTERVAL 1 DAY)     FROM DateSeries     WHERE date < '2021-01-31' ) SELECT * FROM DateSeries;

Рекурсивный CTE в этом запросе создает последовательность дат, что юзабельно для заполнения пропущенных дат в временных рядах или для создания календарей.

Перейдем к задачам

Первая задача

Нужно разработать запрос для идентификации пользователей, которые никогда не испытывали скуку (Bored) в любой рекламной кампании, но испытали восторг (Excited) в последней кампании.

Предполагается наличие таблицы campaigns, которая содержит поля user_id, campaign_id, impression, и timestamp. Поле impression указывает на эмоциональную реакцию пользователя («Bored» или «Excited»), а timestamp фиксирует время реакции.

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

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

WITH LastImpression AS (     SELECT user_id, MAX(timestamp) AS LastTime     FROM campaigns     GROUP BY user_id ), ExcitedLast AS (     SELECT l.user_id     FROM LastImpression l     JOIN campaigns c ON l.user_id = c.user_id AND l.LastTime = c.timestamp     WHERE c.impression = 'Excited' ), NeverBored AS (     SELECT user_id     FROM campaigns     GROUP BY user_id     HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0 ) SELECT n.user_id FROM NeverBored n JOIN ExcitedLast e ON n.user_id = e.user_id;

CTE LastImpression создает временное представление, содержащее user_id и время последнего взаимодействия для каждого пользователя.

CTE ExcitedLast выбирает пользователей, чья последняя реакция была Excited, используя данные из LastImpression.

CTE NeverBored:выбирает пользователей, которые никогда не испытывали скуку. HAVING фильтр здесь проверяет, что суммарное количество Bored впечатлений равно 0.

Финальный SELECT соединяет пользователей из NeverBored и ExcitedLast для получения идентификаторов тех, кто никогда не испытывал скуку, но был восторжен в последней кампании.

Вторая задача

Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года, суммируя затраты за каждый день, если клиент сделал несколько заказов в один день​

Есть таблица orders со следующими колонками:

• customer_id — идентификатор клиента

• order_date — дата заказа

• order_cost — стоимость заказа

Делаем запрос:

SELECT customer_id, SUM(order_cost) AS total_cost FROM (     SELECT customer_id, order_date, SUM(order_cost) AS order_cost     FROM orders     WHERE order_date BETWEEN '2019-02-01' AND '2024-05-31'     GROUP BY customer_id, order_date ) AS daily_orders GROUP BY customer_id ORDER BY total_cost DESC LIMIT 1;

daily_orders: запрос фильтрует заказы, включая только те, что были сделаны с 1 февраля по 31 мая 2024 года.

Далее запрос группирует заказы по customer_id и order_date, суммируя стоимость заказов, сделанных в один день. Это сделано для того, что если клиент сделал несколько заказов в один день, их стоимости будут суммированы.

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

Внешний запрос также сортирует результаты по убыванию общей стоимости заказов и выбирает клиента с наибольшей общей стоимостью заказов, используя LIMIT 1 для возвращения только одного записи с макс. стоимостью.

Третья задача

Напишите запрос для определения трех отделов с наибольшим числом сотрудников, где более 10% сотрудников получают заработную плату выше $100,000.

Предполагается, что есть две таблицы: employees с колонками employee_id, department_id, salary и departments с колонками department_id, department_name.

WITH SalaryStats AS (     SELECT          department_id,         COUNT(*) AS total_employees,         SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners     FROM employees     GROUP BY department_id ), QualifiedDepartments AS (     SELECT          s.department_id,         d.department_name,         s.total_employees,         s.high_earners,         (s.high_earners * 100.0 / s.total_employees) AS percent_high_earners     FROM SalaryStats s     JOIN departments d ON s.department_id = d.department_id     WHERE (s.high_earners * 100.0 / s.total_employees) > 10 ) SELECT      department_id,     department_name,     total_employees,     high_earners,     percent_high_earners FROM QualifiedDepartments ORDER BY total_employees DESC LIMIT 3;

CTE SalaryStats считает общее количество сотрудников в каждом отделе и определяет количество сотрудников с зарплатой выше $100,000, после группирует данные по department_id.

CTE QualifiedDepartments присоединяет информацию о названии отдела из таблицы departments, а после вычисляет процент сотрудников с зарплатой выше $100,000 в каждом отделе и фильтрует отделы, где этот процент больше 10.

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

---

Какой вопрос по SQL на ваших собеседованиях когда-либо вводил вас в заблуждение? Было бы интересно узнать в ваших комментариях, если вопрос будет интересным — добавим его во вторую часть статьи.

Больше практических навыков по SQL и не только вы можете получить в рамках практических онлайн-курсов по аналитике от экспертов отрасли.