What is a CTE (Common Table Expression)? | Interview Question

Q: What is a CTE (Common Table Expression)?

**Target Audience:** Beginners who can write a subquery and want cleaner multi-step queries, and candidates preparing for the performance follow-up. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. CTEs are standard SQL; MySQL added them in 8.0. Version facts are cited inline. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Think of a recipe. A bad one says: "Add to the bowl the mixture of the flour you sifted with the sugar that you creamed with the butter you softened." A good one says: "1. Soften the butter. 2. Cream it with the sugar. 3. Sift in the flour." Same cooking

The problem

Question presented to candidate: "What is a CTE, the WITH clause? Show me one, tell me when you would use it instead of a subquery, and tell me whether it changes performance."

What a strong answer should cover:

A CTE is a named, temporary result set defined with WITH name AS (...) and visible only to the one statement that follows it
Why use it: readability (a query read top to bottom as named steps), referencing the same intermediate result more than once, and chaining steps where each CTE can use the earlier ones
WITH RECURSIVE lets a CTE refer to itself, for hierarchies and generated series (calendars, numbers)
Performance is engine-specific: since PostgreSQL 12 a non-recursive CTE referenced once is inlined like a subquery; MATERIALIZED / NOT MATERIALIZED override that, and SQLite has the same hints since 3.35.0
Differences from a view (stored in the schema, reusable across queries) and a temporary table (persists for the session, can be indexed); PostgreSQL also allows data-modifying CTEs (DELETE ... RETURNING inside WITH)

Code / implementation expected: Yes — a CTE that is referenced twice or chained, and ideally a short recursive one.

Schema used by the examples:

cte

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who can write a subquery and want cleaner multi-step queries, and candidates preparing for the performance follow-up. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. CTEs are standard SQL; MySQL added them in 8.0. Version facts are cited inline.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Think of a recipe. A bad one says: "Add to the bowl the mixture of the flour you sifted with the sugar that you creamed with the butter you softened." A g

The solution is hidden so you can try first

10 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLA CTE referenced twice: customers who spent more than the average customer

SQLChained CTEs: each step reads the one before it

SQLWITH RECURSIVE: a calendar so that days without orders still appear

PostgreSQL: a CTE used once is inlined, so the outer filter reaches the table

EXPLAIN (COSTS OFF)
WITH big_orders AS (
  SELECT id, customer_id, amount FROM orders WHERE amount >= 100
)
SELECT * FROM big_orders WHERE customer_id = 1;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.