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

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

Для аналитиков, владение 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 начиная с начала набора данных до текущей строки.

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

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

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.

CTENeverBored: выбирает пользователей, которые никогда не испытывали скуку. 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 и не только вы можете получить в рамках практических онлайн-курсов по аналитике от экспертов отрасли.