Use CTEs for Complex Queries

You've just inherited a query that's 120 lines long, with four subqueries stacked inside FROM clauses, and you're about to scroll through it to change one filter — sound familiar? Complex SQL doesn't have to be a maze of nested subqueries. Common Table Expressions (CTEs) let you break that beast into named, readable chunks that you can debug, reuse, and even optimize. In this lesson, you'll learn how to use CTEs for complex queries and turn overwhelming SQL into clean, maintainable logic.

The problem this lesson solves

Complex queries — multi-step aggregations, tiered filters, or transformations that need to happen in sequence — are where SQL gets hard to read, hard to debug, and hard to modify. The usual fix, nested subqueries, creates a readability trap: you can't easily test one level without running the whole query, and the indentation alone can make you dizzy. Worse, when you need to reference the same subquery twice (like for a percentage calculation), you have to duplicate the entire block, inviting inconsistency bugs.

CTEs solve this by letting you define a named temporary result set at the top of your query, then reference it like a table. This isn't just cosmetic — it's a mental reframe. You're telling the database: "first, figure out this; then, use that result." This makes your query read like a plan, not a puzzle.

Core concept / mental model

Think of a CTE as a SQL function for a single query. You write a SELECT (or INSERT, UPDATE, DELETE) that produces a result set, give it a name, and then use that name as if it were a real table elsewhere in the same query. It's like declaring a variable in Python before using it in a longer expression.

WITH high_value_orders AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 1000
)
SELECT * FROM high_value_orders;

Key properties of CTEs:

• A CTE exists only within the query it's defined in — it vanishes after execution.
• You can define multiple CTEs in a single WITH clause.
• A CTE can reference a previous CTE in the same WITH list (called a chained CTE).
• PostgreSQL treats CTEs as optimization fences by default, but you can inline them with AS NOT MATERIALIZED (more on that in the troubleshooting section).

Pro tip: A CTE is not a table and not a subquery with benefits — it's a named expression that makes your query's steps explicit.

How it works step by step

Building a complex query with CTEs follows a consistent pattern:

1. Identify your steps — Break your end goal into logical, testable stages. Each stage becomes a CTE.
2. Write each CTE — Use the WITH keyword, give each CTE a descriptive name, and make sure each SELECT is standalone correct.
3. Chain CTEs if needed — Reference earlier CTEs from later ones to build on previous results.
4. Write your final SELECT — The last statement is your main query, which can join or filter against any of the defined CTEs.
5. Test incrementally — Run each CTE separately by temporarily turning it into a standalone SELECT to verify the logic before putting it all together.

Cause and effect here: the cleaner your CTEs, the easier it is to spot a logic error — because you can test each block in isolation, something you can't easily do with deeply nested subqueries.

Hands-on walkthrough

Let's solve a realistic problem: finding top customers by average order value, with their last order date — a query that involves aggregation, window functions, and a final join. Without CTEs, this is a nested nightmare. With CTEs, it's three clean steps.

First, create a sample dataset:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    amount NUMERIC(10,2) NOT NULL,
    order_date DATE NOT NULL
);

INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO orders (customer_id, amount, order_date) VALUES
    (1, 150.00, '2025-01-15'),
    (1, 200.00, '2025-02-20'),
    (2, 90.00, '2025-01-22'),
    (2, 110.00, '2025-03-05'),
    (3, 300.00, '2025-02-01');

Now the CTE breakdown:

WITH order_stats AS (
    -- Step 1: Aggregate orders per customer
    SELECT customer_id,
           COUNT(*) AS order_count,
           AVG(amount) AS avg_amount
    FROM orders
    GROUP BY customer_id
),
last_order AS (
    -- Step 2: Get the most recent order per customer
    SELECT DISTINCT ON (customer_id) customer_id, order_date
    FROM orders
    ORDER BY customer_id, order_date DESC
)
-- Step 3: Combine and rank customers
SELECT c.name,
       os.order_count,
       ROUND(os.avg_amount, 2) AS avg_amount,
       lo.order_date AS last_order_date
FROM customers c
JOIN order_stats os ON c.id = os.customer_id
JOIN last_order lo ON c.id = lo.customer_id
ORDER BY os.avg_amount DESC;

Expected output:

   name   | order_count | avg_amount | last_order_date
----------+-------------+------------+-----------------
 Charlie  |           1 |     300.00 | 2025-02-01
 Alice    |           2 |     175.00 | 2025-02-20
 Bob      |           2 |     100.00 | 2025-03-05

You can also use a CTE in a data-modifying statement, like an UPDATE:

WITH eligible AS (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 150
)
UPDATE customers
SET is_vip = true
WHERE id IN (SELECT customer_id FROM eligible);

Pro tip: When you want to reuse a computed set multiple times in the same query (e.g., for a percentage), define a CTE once and reference it twice — no duplication, fewer inconsistency bugs.

Compare options / when to choose what

CTEs aren't the only tool for decomposing complex queries. Here's a quick comparison:

When to choose CTEs:

• The query needs multiple steps that build on each other.
• You want to debug or test parts of the query independently.
• You need the same computed set more than once.
• Readability is more important than micro-optimizations (usually is in application code).

When to avoid CTEs:

• If performance is critical and the CTE acts as a materialized block that prevents efficient joins — test with EXPLAIN ANALYZE.
• If you need the result across many different queries — use a view or temp table.
• For simple, one-time subqueries, a plain subquery might be shorter.

Troubleshooting & edge cases

1. CTE is slower than expected

By default, PostgreSQL materializes CTEs, meaning it stores the result of each CTE in a temporary data structure before using it. This can hurt performance when the CTE is large and the main query only uses a few rows. Fix: inline the CTE using AS NOT MATERIALIZED.

WITH high_value AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE amount > 500
)
SELECT * FROM high_value WHERE order_date > '2025-01-01';

Pro tip: Use EXPLAIN ANALYZE to compare the MATERIALIZED versus NOT MATERIALIZED versions and see which is faster for your data.

2. "WITH query does not have a return value" — You used a CTE that returns no rows, or your final SELECT forgets to reference the CTE. Make sure the main query actually uses the CTE, or you'll get a warning.

3. CTE names conflict with real tables — If you name a CTE the same as an existing table, the CTE shadows the table inside the query. To avoid confusion, use descriptive names like order_stats instead of orders.

4. Recursive CTEs blow up — If you use WITH RECURSIVE without a proper termination condition, you'll hit an infinite loop. Always include a WHERE clause that eventually stops the recursion.

5. CTE scope — A CTE defined inside a subquery isn't visible outside of it. Each WITH block has its own scope, so plan your nesting carefully.

What you learned & what's next

Let's recap what you've learned, tied to the lesson objectives:

• You can explain the core idea behind CTEs: a named, temporary result set that turns complex queries into readable, modular steps.
• You can apply CTEs in a practical exercise — we built a multi-step query with chained CTEs and even used a CTE in an UPDATE statement.
• You now know how to compare CTEs with subqueries, views, and temp tables, and when to pick each.
• You're aware of the troubleshooting tips — materialization, name conflicts, recursion — to keep your CTE queries fast and correct.

What's next? In the next lesson, you'll learn how to use recursive CTEs — the most powerful form of CTEs — to handle hierarchical data like employee trees or bill-of-materials. Recursive CTEs build on everything you practiced here, so make sure you're comfortable with basic CTEs first. Go write a few CTE queries on your own data tonight — that's the fastest way to make this stick.