Optimized SQL Query: Subqueries vs Joins

Problem Given a schema of 4-5 related tables, write an optimized SQL query that answers a business question, then explain when a subquery should be preferred over a join and when the reverse is true.

Schema

• A typical 4-5 table set: cities(city_id, name), restaurants(restaurant_id, city_id, name), customers(customer_id, city_id, name), orders(order_id, customer_id, restaurant_id, order_value, created_at), payments(payment_id, order_id, status)
• Joins run along the foreign keys; the interviewer supplies the exact columns.

Output

• One row per grouping key of the business question (e.g. per city or per customer) with the requested aggregate columns.
• Plus a verbal account of the execution plan you expect, and why your query shape is the cheaper one.

Example

• "Total order value per city for successfully paid orders" → join orders → restaurants → cities, filter payments.status = 'success', GROUP BY city, SUM(order_value).
• The same logic written as a correlated subquery per city re-executes once for every outer row.