How would you optimize a query with multiple joins and subqueries?

Optimizing multi-join/subquery queries: (1) Reduce join cardinality early—filter before joining. (2) Replace correlated subqueries with JOINs: e.g., SELECT FROM a WHERE a.id IN (SELECT id FROM b WHERE ...) becomes JOIN (SELECT id FROM b WHERE ...) b ON a.id = b.id. (3) Use CTEs for readability and to let optimizer materialize. (4) Ensure join columns are indexed. (5) Join smallest tables first when possible. (6) Avoid SELECT in subqueries. (7) Use EXPLAIN to detect full scans. Example: WITH filtered_orders AS (SELECT FROM orders WHERE status = 'completed'), filtered_items AS (SELECT FROM items WHERE category = 'electronics') SELECT * FROM filtered_orders o JOIN filtered_items i ON o.item_id = i.id; Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.