Multi-Table 3-Way Joins for Business Analytics in SQL

Question

Given `users`, `orders`, and `order_items`, write a SQL query to return `user_name`, `product_category`, and total spend for that category. Only include completed orders.

Accepted Answer

Optimal Solution:
SELECT 
  u.name AS user_name,
  oi.category AS product_category,
  SUM(oi.price * oi.quantity) AS category_spend
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY u.user_id, u.name, oi.category
ORDER BY category_spend DESC;

Technical Explanation:
A 3-table join chains `users -> orders -> order_items` using their respective foreign keys, filters for completed orders, and aggregates spend by user and category.

user_id	order_id	item_id	category	price
1	101	501	Electronics	12000
1	102	502	Books	800

user_name	product_category	category_spend
Aarav	Electronics	12000
Aarav	Books	800