Time to clean up messy nested queries. Same
orders table as before.The old, hard-to-read way (nested subquery):
sql
SELECT customer_id, total_spent
FROM (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 150;
The cleaner way, using a CTE (Common Table Expression):
sql
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 150;
Same result, dramatically more readable - especially once you start chaining multiple CTEs together:
sql
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
),
big_spenders AS (
SELECT customer_id
FROM customer_totals
WHERE total_spent > 150
)
SELECT c.name
FROM customers c
JOIN big_spenders b ON c.id = b.customer_id;
💡 Why interviewers love CTE questions: they reveal whether you can decompose a complex problem into logical, named steps - the same skill you need for clean production SQL, not just passing a test.
⚠️ Performance note: CTEs aren't automatically materialized/cached in every database engine - in some (like older Postgres versions), a CTE could be re-run each time it's referenced. Worth knowing your specific database's behavior before assuming CTEs are always a free readability win.
Do you default to CTEs or subqueries in your day-to-day work? 👇