---

-- 1. Покупатели, сделавшие более 3 заказов
SELECT 
    c.first_name,
    c.last_name,
    c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
HAVING COUNT(o.order_id) > 3;

-- 2. Топ‑5 самых дорогих товаров в каждой категории
WITH ranked_products AS (
    SELECT 
        p.product_name,
        p.price,
        c.category_name,
        ROW_NUMBER() OVER (PARTITION BY c.category_id ORDER BY p.price DESC) AS rn
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
)
SELECT 
    category_name,
    product_name,
    price
FROM ranked_products
WHERE rn <= 5
ORDER BY category_name, price DESC;

-- 3. Средняя сумма заказа за последний месяц (если total_amount есть)
SELECT AVG(total_amount) AS avg_order_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

-- Альтернатива, если total_amount вычисляется через order_items
WITH order_sums AS (
    SELECT 
        o.order_id,
        SUM(oi.quantity * oi.price_at_purchase) AS order_total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 month'
    GROUP BY o.order_id
)
SELECT AVG(order_total) AS avg_order_amount
FROM order_sums;

-- 4. Увеличить цену товаров в категории "Электроника" на 5%
UPDATE products
SET price = price * 1.05
WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Электроника');