SQL Common Table Expressions and Query Structure

A common table expression is the WITH name AS (...) block at the top of a query. It gives a subquery a name so you can refer to it below, the way you’d pull a long expression into a variable in application code. That’s the main reason to use one: structure. The interview-worthy parts are what PostgreSQL does with a CTE at plan time (which changed in version 12) and the less familiar feature of putting INSERT, UPDATE or DELETE inside one.

The plans and timings below come from PostgreSQL 16.15 on a 500,000-row events table with an index on user_id.

Naming steps so each one has a single job

Here’s a report: users who made at least three purchases in September, with their first and last purchase dates and their share of all September purchases. Written as nested subqueries, it reads inside out. Written with CTEs, it reads top to bottom:

WITH september AS (
  SELECT user_id, created_at
  FROM events
  WHERE kind = 'purchase'
    AND created_at >= '2026-09-01' AND created_at < '2026-10-01'
),
per_user AS (
  SELECT user_id,
         count(*)        AS purchases,
         min(created_at) AS first_at,
         max(created_at) AS last_at
  FROM september
  GROUP BY user_id
)
SELECT user_id, purchases, first_at, last_at,
       round(100.0 * purchases / sum(purchases) OVER (), 2) AS pct_of_month
FROM per_user
WHERE purchases >= 3
ORDER BY purchases DESC;

Each block has one responsibility and one grain: september is one row per purchase event, per_user is one row per user. When the numbers look wrong, you can run SELECT * FROM september LIMIT 20 on its own and check that stage, which is the main practical benefit. Debugging nested subqueries means copy-pasting from the middle of a statement.

Note that pct_of_month is computed after WHERE purchases >= 3, so it’s each user’s share among the qualifying users, not among everyone. Structuring the query in named steps makes that kind of decision visible; whether it’s the intended one is a question for whoever asked for the report.

Inlined or materialized: the PostgreSQL 12 change

Before PostgreSQL 12, every CTE was an optimisation fence: PostgreSQL computed the CTE’s full result first, stored it, and then ran the outer query against that stored result. Filters in the outer query couldn’t be pushed into the CTE. A lot of older blog posts and answers still say “CTEs are slow in Postgres” because of this.

Since 12, a CTE that is non-recursive, has no side effects and is referenced exactly once is inlined: the planner treats it like a subquery and optimises across the boundary. You can see both behaviours by asking for them explicitly:

EXPLAIN (COSTS OFF)
WITH purchases AS (SELECT * FROM events WHERE kind = 'purchase')
SELECT * FROM purchases WHERE user_id = 42;

 Bitmap Heap Scan on events
   Recheck Cond: (user_id = 42)
   Filter: (kind = 'purchase'::text)
   ->  Bitmap Index Scan on events_user_id_idx
         Index Cond: (user_id = 42)

The outer user_id = 42 went straight into the index scan. Now force the pre-12 behaviour:

EXPLAIN (COSTS OFF)
WITH purchases AS MATERIALIZED (SELECT * FROM events WHERE kind = 'purchase')
SELECT * FROM purchases WHERE user_id = 42;

 CTE Scan on purchases
   Filter: (user_id = 42)
   CTE purchases
     ->  Seq Scan on events
           Filter: (kind = 'purchase'::text)

PostgreSQL now scans the whole table for purchases and filters for user 42 afterwards. Across three runs on this table, the inlined version executed in 0.1 to 0.9 ms and the materialized one in 41 to 92 ms. The numbers will differ on your machine; the gap in plan shape won’t.

So why does MATERIALIZED exist? Sometimes the fence is what you want. If the planner badly misestimates a complex subquery and picks a nested loop that runs it thousands of times, materializing it once can turn minutes into seconds. Before 12, people used CTEs deliberately for this. Since 12, you say so with AS MATERIALIZED, and you can use AS NOT MATERIALIZED to ask for inlining in cases where PostgreSQL would otherwise materialize.

A CTE referenced twice is computed once

When the outer query references a CTE more than once, PostgreSQL’s default is to materialize it, so the work happens once:

EXPLAIN (COSTS OFF)
WITH p AS (
  SELECT user_id, count(*) AS n FROM events WHERE kind = 'purchase' GROUP BY user_id
)
SELECT a.user_id FROM p a JOIN p b ON b.user_id = a.user_id + 1 WHERE a.n > 5;

 Merge Join
   Merge Cond: (((a.user_id + 1)) = b.user_id)
   CTE p
     ->  HashAggregate
           Group Key: events.user_id
           ->  Seq Scan on events
                 Filter: (kind = 'purchase'::text)
   ->  Sort
         ...
         ->  CTE Scan on p a
   ->  Sort
         ...
         ->  CTE Scan on p b

One HashAggregate feeds both CTE Scan nodes. That’s good for an expensive aggregate. It’s bad when each reference needs a different filter that an index could serve, because the materialized result has no indexes. If you see a slow CTE Scan with a selective Filter underneath, try NOT MATERIALIZED and compare plans.

Writable CTEs: several changes in one statement

A CTE can contain a data-modifying statement with RETURNING, and the outer query can consume those rows. The classic use is moving rows between tables atomically:

WITH moved AS (
  DELETE FROM events
  WHERE created_at < '2026-02-01'
  RETURNING *
)
INSERT INTO events_archive
SELECT * FROM moved;

On the test table this moved 57,365 rows in one statement. Either both the delete and the insert happen or neither does, and you don’t need to hold a list of ids in the application between two round trips.

There’s one rule that catches people. All parts of the statement run against the same snapshot, so the outer query can’t see changes made by the CTE except through RETURNING:

CREATE TABLE t (x int); INSERT INTO t VALUES (1);
WITH u AS (UPDATE t SET x = 2 RETURNING x)
SELECT (SELECT x FROM t) AS seen_by_outer, (SELECT x FROM u) AS returned;

 seen_by_outer | returned
---------------+----------
             1 |        2

The outer query still reads 1 from t. If you need the new values, take them from RETURNING. And don’t update the same row twice from two CTEs in one statement; PostgreSQL’s docs say only one of the modifications will happen and which one isn’t predictable. Data-modifying CTEs are always executed to completion even if the outer query doesn’t read all their output, so they’re never inlined.

Misconceptions worth correcting in an interview

• “CTEs are temporary tables.” They aren’t. They exist only for one statement, have no indexes or statistics, and since 12 may not be stored at all.
• “CTEs are slow in PostgreSQL.” True before 12 for filters that couldn’t be pushed down. Now it depends on materialization, which you can see in EXPLAIN and control.
• “A CTE makes a query faster.” It’s a readability tool first. Rewriting a subquery as an inlined CTE produces the same plan.
• Recursion. WITH RECURSIVE is a different feature that walks hierarchies and graphs; see recursive CTEs.

Interview practice: the report that got slower after a refactor

A teammate rewrote a fast query as WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at > now() - interval '90 days') SELECT ... FROM recent WHERE customer_id = $1. It went from 3 ms to 1.4 seconds. Why, and what would you change?

Answer

MATERIALIZED fences the CTE: PostgreSQL computes every order from the last 90 days across all customers, stores that result, and only then applies customer_id = $1 with a filter over the stored rows, which have no index. The original query let the planner combine both conditions and probably use an index on customer_id or (customer_id, created_at), reading a few dozen rows.

I’d remove MATERIALIZED. Since the CTE is referenced once and has no side effects, PostgreSQL 12 and later will inline it and push the customer filter into the scan. Then I’d confirm with EXPLAIN ANALYZE that customer_id appears in an Index Cond. If the teammate added MATERIALIZED to work around a bad estimate elsewhere in the query, I’d look at that estimate directly (stale statistics, correlated columns) rather than fencing a CTE that doesn’t need it.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• Recursive CTEs
• Reading EXPLAIN ANALYZE
• Official docs: WITH queries