Занятие 27. Практикум 7

🏠 Домашнее задание

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

Задание 1. Продукты без заказов

Найдите продукты, которые ни разу не попадали в order_details. Решите задачу тремя способами:

• с помощью LEFT JOIN;
• с помощью подзапроса и NOT IN;
• с помощью CTE.

Решение через LEFT JOIN

USE northwind;

SELECT p.id, p.product_name
FROM products AS p
LEFT JOIN order_details AS od ON p.id = od.product_id
WHERE od.product_id IS NULL;

Решение через подзапрос

USE northwind;

SELECT id, product_name
FROM products
WHERE id NOT IN (
    SELECT DISTINCT product_id FROM order_details
);

Примечание: в продакшене NOT EXISTS часто предпочтительнее NOT IN, потому что корректно обрабатывает NULL.

Решение через CTE

USE northwind;

WITH ordered_products AS (
    SELECT DISTINCT product_id FROM order_details
)
SELECT id, product_name
FROM products
WHERE id NOT IN (SELECT product_id FROM ordered_products);

Задание 2. Средняя выручка по сотрудникам

Для каждого сотрудника вычислите среднюю выручку с одного заказа. Используйте CTE для подготовки промежуточных сумм по заказам.

USE northwind;

WITH order_revenue AS (
    SELECT o.id AS order_id,
           o.employee_id,
           SUM(od.unit_price * od.quantity) AS revenue
    FROM orders AS o
    JOIN order_details AS od ON o.id = od.order_id
    GROUP BY o.id, o.employee_id
)
SELECT e.first_name, e.last_name, AVG(orv.revenue) AS avg_order_revenue
FROM employees AS e
JOIN order_revenue AS orv ON e.id = orv.employee_id
GROUP BY e.id, e.first_name, e.last_name;

Задание 3. Клиенты с заказами дороже среднего

Найдите клиентов, у которых есть хотя бы один заказ, общая сумма которого превышает среднюю сумму заказа по всей базе. Используйте CTE.

USE northwind;

WITH order_totals AS (
    SELECT o.id AS order_id,
           o.customer_id,
           SUM(od.unit_price * od.quantity) AS total
    FROM orders AS o
    JOIN order_details AS od ON o.id = od.order_id
    GROUP BY o.id, o.customer_id
),
avg_order AS (
    SELECT AVG(total) AS avg_total FROM order_totals
)
SELECT DISTINCT c.first_name, c.last_name
FROM customers AS c
JOIN order_totals AS ot ON c.id = ot.customer_id
WHERE ot.total > (SELECT avg_total FROM avg_order);