Занятие 26. Табличные выражения

✅ Решения заданий

Задание 1. Топ-10 продуктов по заказам и выручке

USE northwind;

WITH product_summary AS (
    SELECT product_id,
           COUNT(*) AS total_orders,
           SUM(unit_price * quantity) AS total_revenue
    FROM order_details
    GROUP BY product_id
)
SELECT p.product_name, ps.total_orders, ps.total_revenue
FROM product_summary AS ps
JOIN products AS p ON ps.product_id = p.id
ORDER BY ps.total_orders DESC
LIMIT 10;

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

Задание 2. Строки дороже средней цены

USE northwind;

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap
    FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price);

Объяснение: CTE возвращает одно число — среднюю цену. Внешний запрос сравнивает каждую строку с этим значением.

Задание 3. Диапазон цен вокруг среднего

USE northwind;

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap
    FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price)
  AND unit_price < (SELECT ap * 1.5 FROM avg_price);

Объяснение: один CTE используется дважды благодаря скалярному подзапросу внутри условий. Это избавляет от дублирования вычисления среднего.

Задание 4. Заказы Sales Representative

USE northwind;

WITH sales_repr AS (
    SELECT id
    FROM employees
    WHERE job_title = 'Sales Representative'
)
SELECT *
FROM orders
WHERE employee_id IN (SELECT id FROM sales_repr);

Объяснение: CTE готовит список идентификаторов сотрудников. Внешний запрос фильтрует заказы через IN. Даже если в CTE один столбец, в условии нужен явный SELECT id FROM sales_repr.