Multiple JOINs

-- Orders with user and product details
SELECT 
    u.name AS customer,
    o.id AS order_id,
    p.name AS product,
    oi.quantity,
    oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- All users with their order totals (include users with no orders)
SELECT 
    u.name,
    u.email,
    COALESCE(SUM(o.total), 0) AS lifetime_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- Users, their orders, and order items (keep all users)
SELECT 
    u.name,
    o.id AS order_id,
    oi.product_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id;

-- Customer order summary with product categories
SELECT 
    u.name AS customer,
    c.name AS category,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS items_bought,
    SUM(oi.price * oi.quantity) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.id, u.name, c.id, c.name
ORDER BY total_spent DESC;