Article
•
CTEs in SQL (Common Table Expressions) – WITH Clause Explained Step by Step (From Noob to Pro)
CTEs in SQL (Common Table Expressions) – WITH Clause Explained Step by Step (From Noob to Pro)
Hey, I’m Gaurav.
When I first saw `WITH` clause in SQL, I thought it was just a fancy way to write a subquery.
I was wrong.
CTEs (Common Table Expressions) became one of the most useful tools in my SQL toolkit — especially for complex queries, interviews, and real-world reporting.
Today, I’ll explain CTEs from the very beginning — how I went from confused to confident.
We’ll cover:
- What is a CTE?
- Why use CTEs instead of subqueries?
- Simple vs Recursive CTEs
- Best practices
All examples are based on our `users`, `orders`, and `products` tables. You can practice every query live here: [sqlpractice.in](https://sqlpractice.in)
---
What is a CTE?
A CTE (Common Table Expression) is a temporary result set that you define at the beginning of your query using the `WITH` clause.
It acts like a named temporary table that exists only for the duration of the query.
Basic Syntax:
```sql
WITH cte_name AS (
-- Your query here
SELECT ...
FROM ...
)
SELECT FROM cte_name;
```
---
Example 1: Simple CTE (Most Common Use)
Question: Find customers who have placed more than 3 orders and show their total spending.
Noob approach (without CTE):
- Write the same JOIN and GROUP BY twice (once in subquery, once in main query) → messy and repetitive.
Better thinking with CTE:
1. First, calculate total orders and spending per customer.
2. Then, filter those who have more than 3 orders.
Clean Solution using CTE:
```sql
WITH customer_stats AS (
SELECT
u.id,
u.name,
COUNT(o.id) AS total_orders,
SUM(o.total_price) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
)
SELECT
name,
total_orders,
total_spent
FROM customer_stats
WHERE total_orders > 3
ORDER BY total_spent DESC;
```
Practice Question 1 (Easy)
Create a CTE to show total orders and spending per customer, then show only those with more than 2 orders.
[Try it live →](https://sqlpractice.in/?question=cte-basic)
---
Why Use CTEs Instead of Subqueries?
Noob thinking:
“Why not just use a subquery?”
Better reasoning:
| Aspect | Subquery | CTE (WITH clause) |
|----------------------|-----------------------------------|---------------------------------------|
| Readability | Hard to read when nested | Much cleaner and easier to read |
| Reusability | Can’t reuse easily | Can be referenced multiple times |
| Debugging | Difficult | Easy — you can SELECT from CTE alone |
| Performance | Usually same | Usually same (or better with optimizer) |
| Complex logic | Becomes messy | Keeps query organized |
Real benefit: When your query has 3–4 levels of logic, CTEs make it readable and maintainable.
---
Example 2: Multiple CTEs (Very Common in Interviews)
You can define multiple CTEs in one query.
Question: Show customers who spent more than average and also placed their first order in 2025.
```sql
WITH customer_stats AS (
SELECT
u.id,
u.name,
SUM(o.total_price) AS total_spent,
MIN(o.ordered_at) AS first_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
),
average_spending AS (
SELECT AVG(total_spent) AS avg_spent
FROM customer_stats
)
SELECT
cs.name,
cs.total_spent,
cs.first_order_date
FROM customer_stats cs
CROSS JOIN average_spending avg
WHERE cs.total_spent > avg.avg_spent
AND cs.first_order_date >= '2025-01-01';
```
Practice Question 2 (Medium)
Create two CTEs: one for customer stats, another for average spending, then show high spenders.
[Try it live →](https://sqlpractice.in/?question=multiple-cte)
---
Example 3: Recursive CTE (Advanced)
A Recursive CTE can reference itself — useful for hierarchical data (org chart, categories, paths).
Simple Example: Generate numbers from 1 to 10.
```sql
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 10
)
SELECT FROM numbers;
```
Practice Question 3 (Hard)
Use recursive CTE to find all subordinates of a manager (if you have manager_id in employees table).
---
How to Think About CTEs (My Mental Model)
When you face a complex query, ask yourself:
1. Can I break this query into smaller logical steps?
2. Do I need the same calculation multiple times?
3. Is the query becoming hard to read?
If the answer is yes to any → Use CTEs.
Best Practice Tips:
- Give meaningful names to CTEs (`customer_stats`, `monthly_sales`, `high_value_customers`)
- Use CTEs to improve readability, not just performance
- You can `SELECT FROM cte_name` during development to debug
- Avoid very deep nesting (more than 4–5 CTEs can become hard to follow)
---
Final Thoughts
I used to write long, nested subqueries and struggle to understand them later.
CTEs changed that completely.
They force you to think in steps:
1. First, get the raw data
2. Then, calculate stats
3. Finally, filter and present
This way of thinking makes complex queries much easier to write, debug, and explain in interviews.
Go practice CTEs on [sqlpractice.in](https://sqlpractice.in) — start with simple ones, then try multiple CTEs.
You’ll quickly see how much cleaner your SQL becomes.
Keep practicing — one concept at a time! 💪
Building sqlpractice.in to help everyone master SQL for interviews — completely free.