Whenever you're ready, walk me through how you'd approach this.
You're a software engineer on HP's supply chain data team. This query powers a dashboard that lists pending print-supply orders for the West region, and it has gotten so slow that the dashboard times out as order volume has grown.
-- orders(order_id, customer_id, region, status, created_at)
-- order_items(item_id, order_id, product_id, quantity, unit_price)
SELECT DISTINCT o.order_id, o.customer_id, o.region, o.created_at,
(SELECT SUM(oi.quantity * oi.unit_price)
FROM order_items oi
WHERE oi.order_id = o.order_id) AS order_total,
(SELECT COUNT(*)
FROM order_items oi2
WHERE oi2.order_id = o.order_id) AS item_count
FROM orders o
WHERE UPPER(o.status) = 'PENDING'
AND o.region LIKE '%WEST%'
ORDER BY order_total DESC;
Explain why this query is slow and unlikely to scale as the orders table grows, and describe exactly how you would rewrite it. You don't need to submit a full patch, just walk through each performance problem and your fix for it.