hw#6

-- Problem #1
SELECT
    COUNT(order_id),
    SUM(tax_amount)
FROM orders;

-- Problem #2
SELECT
    category_name,
    COUNT(*) AS product_count,
    MAX(list_Price) AS most_expensive_product
FROM categories
JOIN products ON products.category_id = categories.category_id
GROUP BY category_name;

-- Problem #3
SELECT
    email_address,
    SUM(item_price * quantity),
    SUM(discount_amount * quantity)
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY email_address
ORDER BY item_price DESC;

-- Problem #4
SELECT
    email_address,
    COUNT(item_id),
    (item_price - discount_amount) * quantity
FROM customers
JOIN orders ON orders.customer_id = customers.customer_id
JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY email_address
HAVING COUNT(item_id) > 1
ORDER BY ((item_price - discount_amount) * quantity) DESC;

-- Problem #5
SELECT
    email_address,
    COUNT(item_id),
    (item_price - discount_amount) * quantity
FROM customers
JOIN orders ON orders.customer_id = customers.customer_id
JOIN order_items ON orders.order_id = order_items.order_id AND item_price >400
GROUP BY email_address
HAVING COUNT(item_id) > 1
ORDER BY ((item_price - discount_amount) * quantity) DESC;