Generate SQL Queries with Complex Joins

```sql -- Customer orders with product details for last 30 days SELECT c.first_name, c.last_name, o.order_date, p.product_name, oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price) AS line_total FROM customers c INNER JOIN orders o ON c.id = o.customer_id -- Only customers with orders INNER JOIN order_items oi ON o.id = oi.order_id -- Only orders with items INNER JOIN products p ON oi.product_id = p.id -- Get product details WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days' AND c.status = 'active' ORDER BY c.last_name ASC, c.first_name ASC, o.order_date DESC; ``` **Performance Considerations:** - Add indexes on customer_id, order_id, and product_id for faster joins - Consider adding index on order_date for the date filter - Query will benefit from composite index on (status, id) for customers table **Alternative Approach:** You could use LEFT JOINs if you want to include customers with no recent orders, but filter them out in a HAVING clause. **Expected Result Structure:** Returns one row per order item, showing customer info, order date, product details, and calculated line totals, sorted by customer name then order date.