← Все темы

SQL практика

Вопросов: 24

Решение задачи 1: 
  
SELECT e.name, d.department_name  
FROM employees e  
INNER JOIN departments d ON e.department_id = d.department_id;


Объяснение:
- Используем INNER JOIN для соединения таблиц employees и departments по полю department_id.
- Такой тип соединения возвращает только тех сотрудников, у которых есть соответствующий отдел (то есть department_id в employees соответствует department_id в departments).
- В результате выводим имя сотрудника и название его отдела. 


SELECT 
  o.order_id,
  c.customer_name,
  COALESCE(SUM(oi.quantity), 0) AS total_quantity
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.customer_name;


Объяснение:
- Используем LEFT JOIN от таблицы orders к customers, чтобы получить имя клиента для каждого заказа.
- Далее с помощью LEFT JOIN связываем order_items, чтобы считать количество товаров по каждому заказу.
- Используем агрегацию SUM(quantity) для подсчёта количества товаров. Для заказов без товаров SUM вернёт NULL, поэтому используем COALESCE для замены на 0.
- Группируем по order_id и customer_name, чтобы агрегировать количество товаров именно по каждому заказу.
Так мы гарантируем, что будут включены все заказы, даже если для них нет записей в order_items. 


SELECT 
    p.product_id,
    p.product_name,
    c.category_name,
    COUNT(p2.product_id) AS products_in_category
FROM 
    categories c
    LEFT JOIN products p ON c.category_id = p.category_id
    LEFT JOIN products p2 ON c.category_id = p2.category_id
GROUP BY 
    p.product_id,
    p.product_name,
    c.category_name
ORDER BY 
    c.category_name, p.product_name;


Объяснение:
- Используем LEFT JOIN от таблицы categories к products, чтобы вывести все категории, включая пустые (без продуктов).
- Дополнительно связали таблицу products как p2 для подсчёта количества продуктов в каждой категории.
- GROUP BY по идентификаторам и именам продукта, а также имени категории, чтобы получить количество продуктов в категории в каждой строке.
- Таким образом мы получаем для каждой категории все её продукты, при этом категории без продуктов тоже отображаются с количеством 0.
- RIGHT JOIN здесь не обязателен, потому что левое соединение LEFT JOIN от категорий к продуктам уже обеспечивает нужный результат.
- Если строго необходим RIGHT JOIN, его можно заменить на LEFT JOIN наоборот, но по смыслу этот запрос проще и читаемее именно с LEFT JOIN от категорий.

Решение: 


SELECT e.emp_id, e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;


Объяснение:
- Используется SELF JOIN таблицы employees, где таблица e представляет сотрудников, а таблица m — менеджеров.
- Соединение происходит по условию, что e.manager_id = m.emp_id.
- Используется LEFT JOIN, чтобы вывести всех сотрудников, включая тех, у кого нет менеджера (в таких случаях имя менеджера будет NULL). 


SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > (
        SELECT AVG(total_sum)
        FROM (
            SELECT SUM(total_amount) AS total_sum
            FROM orders
            GROUP BY customer_id
        ) AS customer_sums
    )
);


Объяснение:
- Внутренний самый вложенный подзапрос SELECT SUM(total_amount) ... GROUP BY customer_id вычисляет сумму заказов по каждому клиенту.
- Затем внешний подзапрос SELECT AVG(total_sum) FROM (...) вычисляет среднее значение этих сумм по всем клиентам.
- В основном запросе выбираются имена клиентов, у которых сумма заказов (группировка по customer_id) больше полученного среднего значения.
- Используя HAVING с подзапросом, мы фильтруем по агрегатным значениям.
- Сопоставление customer_id в основном запросе и подзапросе позволяет вывести именно тех клиентов, чьи суммы выше среднего.

Решение задачи 6: 


SELECT 
  d.department_name, 
  AVG(e.salary) AS avg_salary
FROM 
  employees e
JOIN 
  departments d ON e.department_id = d.department_id
GROUP BY 
  d.department_name
HAVING 
  AVG(e.salary) > (
    SELECT AVG(salary) FROM employees
  );


Объяснение:
- В основной части запроса мы группируем сотрудников по отделам и считаем среднюю зарплату в каждом отделе.
- В подзапросе в условии HAVING вычисляется средняя зарплата по всем сотрудникам (всем отделам) сразу.
- Далее сравниваем среднюю зарплату каждого отдела с общей средней зарплатой и фильтруем только те, где она выше.
- В результате получаем название отдела и его среднюю зарплату, которая превышает среднюю по всем отделам. 


SELECT p.product_id, p.product_name
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE s.quantity = (
    SELECT MAX(quantity)
    FROM sales
);


Объяснение:
- Во внешнем запросе мы соединяем таблицы products и sales по product_id.
- В подзапросе вычисляем максимальное количество продаж MAX(quantity) среди всех записей в таблице sales.
- Во внешнем запросе фильтруем записи по условию, что количество продаж за один день равно максимальному найденному значению.
- В результате выводятся продукты, которые имели максимальное количество продаж за один день.

Решение: 

SELECT emp_id, name, salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE emp_id = 10
);


Объяснение:
- В подзапросе выбирается зарплата сотрудника с emp_id = 10.
- Этот подзапрос возвращает одно скалярное значение — зарплату указанного сотрудника.
- В основном запросе фильтруются все сотрудники, у которых зарплата больше этого значения.
- Таким образом, мы получаем список сотрудников с зарплатой выше зарплаты сотрудника с id = 10. 

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(DISTINCT orders.order_id) AS total_orders,
    SUM(order_items.quantity * order_items.price) AS total_sales
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY month
ORDER BY month;


Объяснение:
- Используем JOIN для объединения таблиц orders и order_items по order_id.
- Группируем данные по месяцу с помощью функции DATE_FORMAT, которая извлекает год и месяц из order_date.
- Считаем количество уникальных заказов с помощью COUNT(DISTINCT orders.order_id), чтобы не учитывать дубликаты из-за множества товаров в заказе.
- Вычисляем общую сумму продаж как сумму произведения quantity * price.
- Результат сортируется по месяцу для удобства анализа. 

  
SELECT 
    department_id, 
    AVG(salary) AS avg_salary, 
    MIN(salary) AS min_salary, 
    MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;


Объяснение:
- Запрос выбирает поле department_id для группировки данных по каждому департаменту.
- Затем вычисляются три агрегатные функции на поле salary:
- AVG() — среднее значение зарплаты,
- MIN() — минимальная зарплата,
- MAX() — максимальная зарплата.
- Использование GROUP BY department_id позволяет получить агрегаты отдельно для каждого департамента.
- В результате выводится таблица с колонками: department_id, avg_salary, min_salary, max_salary. 


SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
ORDER BY avg_price DESC
LIMIT 1;


Объяснение:
- Сначала происходит группировка данных по category_id.
- Для каждой категории вычисляется средняя цена продуктов с помощью функции AVG(price).
- Результат сортируется по средней цене в порядке убывания (DESC), чтобы категории с самой высокой средней ценой оказались вверху.
- Ограничение LIMIT 1 выбирает только запись с максимальной средней ценой, то есть категорию с самой высокой средней ценой продукта. 

SELECT  
    EXTRACT(YEAR FROM sale_date) AS sale_year,  
    SUM(quantity) AS total_quantity_sold,  
    COUNT(DISTINCT product_id) AS unique_products_sold  
FROM sales  
GROUP BY sale_year  
ORDER BY sale_year;


Объяснение:
- EXTRACT(YEAR FROM sale_date) извлекает год из даты продажи, по которому выполняется группировка.
- SUM(quantity) суммирует количество проданных товаров в каждом году.
- COUNT(DISTINCT product_id) подсчитывает количество уникальных продуктов, которые были проданы в каждом году, исключая повторения.
- Группировка по году обеспечивает агрегацию данных за каждый отчетный период.
- ORDER BY sale_year упорядочивает результат по году для удобства анализа.

Решение: 

SELECT 
  emp_id,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;


Объяснение:
- RANK() OVER создаёт ранжирование строк с учетом их значения.
- PARTITION BY department_id разделяет данные на группы по отделам, чтобы ранги считались внутри каждого отдела отдельно.
- ORDER BY salary DESC сортирует сотрудников в каждом отделе по убыванию зарплаты, таким образом самая высокая зарплата получает ранг 1.
- Если несколько сотрудников имеют одинаковую зарплату, им присваивается одинаковый ранг, а следующий ранг пропускается (типичный функционал RANK()).

Решение задачи 14: 

SELECT 
  sale_id,
  customer_id,
  sale_date,
  total,
  SUM(total) OVER (
    PARTITION BY customer_id 
    ORDER BY sale_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_total
FROM sales;


Объяснение:
- SUM(total) OVER — аналитическая функция, вычисляющая скользящую сумму.
- PARTITION BY customer_id — разбивает данные по каждому клиенту отдельно.
- ORDER BY sale_date — упорядочивает заказы клиента по дате покупки, чтобы сумма накапливалась хронологически.
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — определяет диапазон, для которого считается сумма: от начала раздела до текущей строки включительно.
- В итоге для каждого заказа показывается накопительная сумма total по данному клиенту, что полезно для анализа динамики покупок.

SQL-запрос: 

SELECT 
    emp_id, 
    salary, 
    salary - AVG(salary) OVER (PARTITION BY department_id) AS salary_difference
FROM employees;


Объяснение:
- Функция AVG(salary) OVER (PARTITION BY department_id) вычисляет среднюю зарплату по каждому отделу отдельно.
- Для каждого сотрудника берётся его зарплата salary и вычитается средняя зарплата его отдела, что даёт разницу.
- Такой подход позволяет получить нужный результат без группировки и с сохранением информации о каждом сотруднике. 


SELECT 
    c.country,
    o.customer_id,
    COUNT(o.order_id) AS customer_order_count,
    SUM(COUNT(o.order_id)) OVER (PARTITION BY c.country) AS country_order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.country, o.customer_id
ORDER BY c.country, o.customer_id;


Объяснение:
- COUNT(o.order_id) подсчитывает количество заказов для каждого клиента (GROUP BY c.country, o.customer_id).
- SUM(COUNT(o.order_id)) OVER (PARTITION BY c.country) – это оконная функция, которая суммирует количество заказов всех клиентов в пределах одной страны, то есть возвращает общее число заказов по стране для каждой строки с этим country.
- Таким образом, в каждой строке выводится страна, клиент, количество заказов данного клиента и общее количество заказов в стране.

Решение: 

SELECT 
  emp_id,
  department_id,
  salary,
  LAG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS previous_salary
FROM employees;


Объяснение:
- Функция LAG() позволяет получить значение из предыдущей строки в рамках окна.
- Ключевое слово OVER определяет окно, на котором работает функция.
- PARTITION BY department_id группирует сотрудников по отделам, чтобы поиск "предыдущей зарплаты" происходил внутри каждого отдела отдельно.
- ORDER BY hire_date упорядочивает сотрудников в отделе по дате найма, чтобы "предыдущим" считался сотрудник, нанятый непосредственно перед текущим.
- В итоге для каждого сотрудника выводится его зарплата и зарплата сотрудника, который был нанят раньше в том же отделе. Если предыдущего сотрудника в отделе нет, то значение previous_salary будет NULL.

Решение: 

SELECT 
    sale_id,
    product_id,
    sale_date,
    quantity,
    AVG(quantity) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3_days
FROM sales
ORDER BY product_id, sale_date;


Объяснение:
- Используется оконная функция AVG(), которая вычисляет среднее значение в заданном окне.
- Ключевое слово PARTITION BY product_id разбивает данные по каждому продукту, так что вычисления выполняются в рамках каждого продукта отдельно.
- ORDER BY sale_date упорядочивает данные по дате продажи в каждой группе.
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW задаёт окно, включающее текущую строку и две предыдущие, то есть последние 3 дня с текущей датой (если есть данные).
- Таким образом, для каждой записи считается среднее количество продаж за текущий день и два предыдущих, реализуя скользящее среднее за 3 дня по каждому продукту.

Решение: 

SELECT 
    emp_id,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id 
        ORDER BY emp_id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_avg_salary
FROM employees
ORDER BY department_id, emp_id;


Объяснение:
- Оконная функция AVG(salary) OVER (...) вычисляет среднее значение зарплаты с накоплением.
- PARTITION BY department_id разбивает данные по отделам, то есть для каждого отдела считается средняя отдельно.
- ORDER BY emp_id упорядочивает сотрудников внутри отдела по возрастанию идентификатора.
- Ключевая часть — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW задаёт окно от первой до текущей записи в пределах группы и порядка. То есть среднее считается по всем сотрудникам отдела с наименьшим emp_id до текущего включительно.
- Такой подход позволяет получить накопительное среднее для каждого emp_id в отделе. 


SELECT 
  product_id, 
  category_id, 
  price,
  MIN(price) OVER (PARTITION BY category_id) AS min_price_in_category,
  MAX(price) OVER (PARTITION BY category_id) AS max_price_in_category
FROM products;


Объяснение:
Данный запрос использует оконные функции MIN() и MAX() с конструкцией OVER (PARTITION BY category_id). Это означает, что для каждой строки (продукта) агрегатные функции считаются отдельно в группе продуктов, объединённых по category_id.
- MIN(price) возвращает минимальную цену внутри категории для каждой строки, не уменьшая количество возвращаемых строк.
- Аналогично, MAX(price) возвращает максимальную цену внутри той же категории.
Таким образом, для каждого продукта выводятся его собственные данные и границы цен его категории. 


WITH RankedEmployees AS (
    SELECT 
        emp_id, 
        name, 
        department_id, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT 
    emp_id, 
    name, 
    department_id, 
    salary
FROM RankedEmployees
WHERE rn <= 3;


Объяснение:
- CTE RankedEmployees создаёт временную таблицу, в которой для каждого сотрудника определяется его ранг (rn) внутри департамента на основе зарплаты по убыванию с помощью функции ROW_NUMBER() с разделением по department_id.
- Далее из этого CTE выбираются только те записи, у которых rn ≤ 3, то есть топ-3 сотрудника с самой высокой зарплатой в каждом департаменте.
- Такое решение корректно работает для каждого отдела отдельно и использует преимущества CTE для читаемости и структурированности запроса. 


WITH OrderTotals AS (
    SELECT 
        o.order_id,
        SUM(oi.quantity * oi.price) AS total_amount
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.order_id
)
SELECT 
    order_id,
    total_amount
FROM OrderTotals
WHERE total_amount > 1000;


Объяснение:
- Сначала создаём CTE OrderTotals, в котором для каждого order_id считаем общую сумму заказа как произведение quantity на price для всех позиций этого заказа и складываем их.
- Затем основной запрос выбирает из CTE только те заказы, у которых общая сумма превышает 1000.
- Такой подход позволяет разбить вычисления на этапы: сначала агрегируем данные по заказам, а потом уже фильтруем по результатам агрегирования. 


WITH RECURSIVE Subordinates AS (
    -- Базовый кейс: выбираем сотрудника с emp_id = 1
    SELECT emp_id, name, manager_id
    FROM employees
    WHERE emp_id = 1

    UNION ALL

    -- Рекурсивный кейс: выбираем сотрудников, у которых manager_id равен emp_id из предыдущего уровня
    SELECT e.emp_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.emp_id
)
-- Исключаем самого начальника (emp_id = 1)
SELECT emp_id, name, manager_id
FROM Subordinates
WHERE emp_id <> 1;

Объяснение:
- В рекурсивном CTE Subordinates сначала выбирается начальник с emp_id = 1.
- Далее рекурсивно выбираются все сотрудники, у которых manager_id соответствует сотрудникам из предыдущего уровня, тем самым строится иерархия подчинённых любого уровня вложенности.
- Итоговый запрос выводит всех найденных подчинённых, исключая самого начальника.

Решение задачи: 


WITH daily_sales AS (
    SELECT 
        sale_date, 
        SUM(quantity) AS total_quantity
    FROM 
        sales
    GROUP BY 
        sale_date
)
SELECT 
    sale_date, 
    total_quantity,
    RANK() OVER (ORDER BY total_quantity DESC) AS rank
FROM 
    daily_sales;


Объяснение:
- В CTE daily_sales агрегируем данные, группируя по дате продажи, вычисляя сумму продаж quantity за каждый день.
- На основном запросе используем оконную функцию RANK(), чтобы присвоить ранги дням, упорядоченным по убыванию суммы продаж total_quantity.
- Таким образом, дни с наибольшей суммой продаж получат ранг 1, следующие — ранг 2 и так далее.
- Использование CTE облегчает читаемость и повторное использование агрегированных данных для оконной функции.