Many real analysis questions need more than one step: “find customers whose total spend is above the average”, or “show each month’s revenue next to the previous month”. SQL gives you two ways to break such problems into steps: subqueries and CTEs (common table expressions).
Subqueries
A subquery is a query inside another query, written in brackets:
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT AVG(amount) FROM orders);
The inner query runs first and returns one number, which the outer query uses. Subqueries are fine for short, single-step logic.
CTEs
A CTE gives a name to an intermediate result using WITH:
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
)
SELECT * FROM customer_totals
WHERE total > (SELECT AVG(total) FROM customer_totals);
The query now reads top to bottom like a recipe: first build customer totals, then filter them.
When to use which
- Use a subquery for a quick, one-off value, such as a single average in a WHERE clause.
- Use a CTE when the query has several steps, when you need the same intermediate result twice, or when someone else will read and maintain the query.
- Recursive CTEs can walk hierarchies, such as an employee and manager tree, which subqueries cannot do neatly.
Performance
In most modern databases, a CTE and an equivalent subquery perform similarly, because the optimiser rewrites both. Choose based on readability first, then check the query plan if something is slow.
Practice task
Using an orders table, write a CTE that calculates monthly revenue, then a second CTE that adds the previous month’s revenue with LAG(), and finally select months where revenue grew.
Related: SQL JOINs explained and SQL window functions.
SQL is taught with real datasets in our Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
