Write an Optimized SQL Query: Subqueries vs Joins

Problem: Given 4-5 tables, write an optimized SQL query to answer a business question, and explain when subqueries should be preferred over joins (and vice versa).

Approach: A query using nested subqueries is often not the most optimal choice compared to rewriting the same logic as a JOIN, because the query planner/optimizer can generally produce a more efficient execution plan for joins (avoiding repeated subquery re-execution per outer row in correlated subqueries). Prefer JOINs when combining rows from multiple tables based on a relationship; prefer subqueries for existence checks (EXISTS) or when a scalar single value needs to be computed per row and a join would create unwanted row duplication.