Write Your SQL Like a Pro: Mastering Common Table Expressions (CTEs)

If you've ever stared at a deeply nested SQL query and wondered who wrote that monster — chances are it could have been written much more cleanly with a CTE.

Common Table Expressions (CTEs) are one of the most powerful readability and maintainability tools in SQL. They let you break a complex query into named, logical building blocks that read almost like a story. Once you start using them, you'll wonder how you ever got along without them.

In this guide, you'll learn what CTEs are, how to write them, how to chain multiple CTEs together, and some real-world scenarios where they shine.

---

What Is a CTE?

A CTE — defined with the WITH keyword — is a temporary named result set that exists only for the duration of a single query. Think of it like a temporary view you define at the top of your query and then reference below.

Basic syntax:

WITH cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

That's it. You define the CTE inside parentheses after AS, name it, and then use it in the main query below like any regular table.

---

Your First CTE: A Simple Example

Say you have an orders table and you want to find all customers who placed more than 5 orders. You could write this as a subquery:

-- Subquery approach (harder to read)
SELECT customer_id, order_count
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS order_summary
WHERE order_count > 5;

Here's the same logic as a CTE:

-- CTE approach (much easier to read)
WITH order_summary AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, order_count
FROM order_summary
WHERE order_count > 5;

Both queries return the same result, but the CTE version makes the intent clear immediately. The named block order_summary tells you exactly what it contains before you even read it.

---

Chaining Multiple CTEs

Here's where CTEs get really powerful: you can define multiple CTEs in a single query, each building on the previous. Separate them with commas.

Let's say you're working with an e-commerce database and you want to find the top 10 customers by revenue, but only count orders that were actually delivered (not cancelled or refunded).

Tables:

• orders(order_id, customer_id, status, created_at)
• order_items(order_id, product_id, quantity, unit_price)
• customers(customer_id, name, email, country)

WITH 
-- Step 1: Only look at delivered orders
delivered_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE status = 'delivered'
),

-- Step 2: Calculate revenue per order
order_revenue AS (
    SELECT 
        do.customer_id,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM delivered_orders do
    JOIN order_items oi ON oi.order_id = do.order_id
    GROUP BY do.customer_id
),

-- Step 3: Rank customers by total revenue
ranked_customers AS (
    SELECT 
        or.customer_id,
        or.revenue,
        RANK() OVER (ORDER BY or.revenue DESC) AS revenue_rank
    FROM order_revenue or
)

-- Final query: Get the top 10
SELECT 
    c.name,
    c.email,
    rc.revenue,
    rc.revenue_rank
FROM ranked_customers rc
JOIN customers c ON c.customer_id = rc.customer_id
WHERE rc.revenue_rank <= 10
ORDER BY rc.revenue_rank;

Notice how each CTE has one clear job. delivered_orders filters, order_revenue aggregates, ranked_customers ranks. Reading this query top to bottom tells the story of what you're doing — no nested parentheses archaeology required.

---

Using CTEs with UPDATE and DELETE

CTEs aren't just for SELECT queries. You can use them with UPDATE and DELETE too, which is incredibly handy for complex conditional modifications.

Example: Archive orders older than 2 years

WITH old_orders AS (
    SELECT order_id
    FROM orders
    WHERE created_at < NOW() - INTERVAL '2 years'
      AND status IN ('delivered', 'cancelled')
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM old_orders);

Or with UPDATE — say you want to flag high-value customers:

WITH high_value_customers AS (
    SELECT customer_id
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 10000
)
UPDATE customers
SET tier = 'platinum'
WHERE customer_id IN (SELECT customer_id FROM high_value_customers);

---

Real-World Use Case: Running Totals

CTEs pair beautifully with window functions. Here's a classic example — a running total of daily sales:

WITH daily_sales AS (
    SELECT 
        DATE(created_at) AS sale_date,
        SUM(total_amount) AS daily_total
    FROM orders
    WHERE status = 'delivered'
    GROUP BY DATE(created_at)
)
SELECT 
    sale_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;

Sample output:

sale_date	daily_total	running_total
2026-01-01	4200.00	4200.00
2026-01-02	3800.00	8000.00
2026-01-03	5100.00	13100.00

---

CTEs vs. Subqueries: When to Use Which?

Feature	CTE	Subquery
Readability	✅ Much easier for complex logic	❌ Can get deeply nested
Reusability within query	✅ Reference multiple times	❌ Must repeat the query
Recursive queries	✅ Supported	❌ Not supported
Performance	Roughly equivalent	Roughly equivalent
Short, simple filters	🤷 Overkill	✅ Fine for one-liners

The general rule: if your subquery is more than 5–6 lines, or you need to reference it more than once, use a CTE.

---

Common Mistakes to Avoid

1. Trying to reference a CTE outside its query

CTEs only live within the single statement they're defined in. You cannot define a CTE in one query and use it in another.

-- ❌ This does NOT work
WITH my_cte AS (SELECT * FROM products)

-- ... some other SQL here ...

SELECT * FROM my_cte; -- ERROR: my_cte doesn't exist here

2. Forgetting the comma between multiple CTEs

-- ❌ Missing comma — will throw a syntax error
WITH cte_one AS (
    SELECT ...
)
cte_two AS (   -- needs a comma after the closing parenthesis above
    SELECT ...
)
SELECT * FROM cte_two;

-- ✅ Correct
WITH cte_one AS (
    SELECT ...
),
cte_two AS (
    SELECT ...
)
SELECT * FROM cte_two;

3. Overusing CTEs for simple queries

CTEs add clarity, not magic. For a simple single-table SELECT, a CTE just adds boilerplate. Use them when they actually help readability.

---

Key Takeaways

• CTEs are defined with the WITH keyword and give you a named, temporary result set
• They dramatically improve readability for complex queries
• You can chain multiple CTEs in a single query, each building on the last
• CTEs work with SELECT, INSERT, UPDATE, and DELETE
• They're the natural home for queries you'd otherwise write as deeply nested subqueries
• Performance is roughly equivalent to subqueries in most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.)

---

What's Next?

Once you're comfortable with regular CTEs, the next step is recursive CTEs — which allow a CTE to call itself, making them perfect for traversing hierarchical data like org charts, category trees, or folder structures. That's a topic for another day, but knowing that CTEs can be recursive shows just how powerful this feature really is.

Have you been using CTEs in your projects? Drop a comment below — I'd love to hear about any creative use cases you've found. And if this cleared up any confusion, share it with a teammate who's still writing those 10-level-deep subqueries! 😅