Common Table Expressions (CTEs) Interview Questions & Answers

A CTE is a named temporary result set, defined with the WITH clause, that exists only for the duration of a single statement. You define it once, then reference it by name in the query that follows — like a disposable, inline view.

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2026-01-01'
)
SELECT customer_id, COUNT(*)
FROM   recent_orders          -- reference the CTE by name
GROUP  BY customer_id;

CTEs make complex queries readable by breaking them into named, top-to-bottom steps instead of deeply nested subqueries.

Rule of thumb: a CTE is a named subquery you define up front with WITH to make a query read like sequential steps.

A CTE starts with WITH, names the result set, and defines it in parentheses; the main query follows and references the name.

WITH cte_name (optional, col, list) AS (
    SELECT ...               -- the CTE body
)
SELECT * FROM cte_name;      -- the main query MUST follow immediately

Key rules: the main statement must come right after the CTE definition; the optional column list renames the output columns; and the CTE is only visible to the statement it's attached to.

Rule of thumb: WITH name AS (...) then the query — the CTE and its consumer are one statement.

Functionally a CTE is similar to a derived-table subquery, but it offers things a plain subquery can't:

• Reusability — reference the same CTE multiple times in one query; a derived table must be repeated.
• Readability — named, top-to-bottom steps instead of inside-out nesting.
• Recursion — only CTEs can be recursive.

-- a subquery repeated twice...
SELECT * FROM (SELECT ...) a JOIN (SELECT ...) b ON ...;

-- ...vs a CTE defined once, used twice
WITH t AS (SELECT ...)
SELECT * FROM t a JOIN t b ON ...;

Performance is usually comparable — many engines treat a CTE as syntactic sugar for a subquery.

Rule of thumb: prefer a CTE when logic is reused, recursive, or complex enough that naming the steps helps.

Both name a query, but their scope and persistence differ:

• A view is a permanent schema object, stored in the catalog and reusable by any query and user, until dropped.
• A CTE is temporary and scoped to a single statement — it vanishes when the query finishes and isn't stored anywhere.

-- view: created once, reused forever
CREATE VIEW active_customers AS SELECT * FROM customers WHERE active = true;

-- CTE: lives only inside this one statement
WITH active_customers AS (SELECT * FROM customers WHERE active = true)
SELECT * FROM active_customers;

Rule of thumb: reuse across many queries → create a view; one-off, query-local logic → use a CTE.

Yes. Write a single WITH, then separate each CTE definition with a comma. They're listed top-to-bottom and any CTE can reference the ones defined before it.

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM   employees GROUP BY dept_id
),
high_paying AS (
    SELECT dept_id FROM dept_avg WHERE avg_sal > 80000  -- uses dept_avg
)
SELECT e.name
FROM   employees e
JOIN   high_paying h ON e.dept_id = h.dept_id;

Only one WITH keyword is needed regardless of how many CTEs follow.

Rule of thumb: chain CTEs with commas to build a pipeline of named steps, each able to use the ones above it.

Because a CTE can reference earlier CTEs, you can express a data pipeline as a sequence of transformations — each step reads cleanly off the previous one.

WITH raw AS (
    SELECT customer_id, total FROM orders WHERE status = 'paid'
),
per_customer AS (
    SELECT customer_id, SUM(total) AS spend FROM raw GROUP BY customer_id
),
ranked AS (
    SELECT customer_id, spend,
           RANK() OVER (ORDER BY spend DESC) AS rnk
    FROM per_customer
)
SELECT * FROM ranked WHERE rnk <= 10;   -- top 10 spenders

This is far more readable than three levels of nested subqueries.

Rule of thumb: model ETL-style logic as chained CTEs — filter, aggregate, rank, then select.

A recursive CTE references itself to process hierarchical or iterative data — org charts, category trees, graph traversal, or number/date series. It's declared with WITH RECURSIVE (the keyword is optional in SQL Server).

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id          -- anchor: the starting row(s)
    FROM   employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id    -- recursive: joins back to the CTE
    FROM   employees e
    JOIN   subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

It runs the anchor once, then repeatedly runs the recursive part against the previous result until no new rows appear.

Rule of thumb: use a recursive CTE to walk hierarchies and trees that a flat JOIN can't express.

A recursive CTE has two members joined by UNION ALL:

1. Anchor member — the non-recursive base case; runs once to seed the result (e.g. the root of a tree).
2. Recursive member — references the CTE itself; runs repeatedly, each iteration operating on the rows the previous iteration produced.

WITH RECURSIVE nums AS (
    SELECT 1 AS n                     -- anchor
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5 -- recursive, with a stop condition
)
SELECT n FROM nums;   -- 1,2,3,4,5

The recursive member must have a terminating condition or the query loops until it hits the recursion limit.

Rule of thumb: anchor seeds, recursive member iterates, UNION ALL glues them — always include a stopping condition.

A recursive CTE loops forever if the recursive member never stops producing new rows — common with cyclic data (A reports to B, B reports to A).

Defenses:

• A terminating predicate in the recursive member (WHERE n < 100, WHERE level < 10).
• Track a path / visited set and exclude already-seen nodes to break cycles.
• Rely on the engine's recursion limit as a safety net (Postgres has no hard default but you can LIMIT; SQL Server defaults to MAXRECURSION 100).

-- SQL Server: cap iterations explicitly
SELECT * FROM subordinates OPTION (MAXRECURSION 50);

Rule of thumb: every recursive CTE needs a stop condition; for graphs that may contain cycles, also track visited nodes.

Yes — a key advantage over a derived table. Define the CTE once and use its name as many times as needed, including self-joins.

-- compare each employee's salary to their manager's, using one CTE twice
WITH emp AS (
    SELECT id, name, salary, manager_id FROM employees
)
SELECT e.name, e.salary, m.salary AS manager_salary
FROM   emp e
JOIN   emp m ON e.manager_id = m.id;

Whether the engine computes the CTE once and caches it or re-evaluates per reference depends on the database and whether it's materialized.

Rule of thumb: reuse a CTE by name instead of copy-pasting the same subquery — define once, reference freely.

It depends on the database. Materialized means the CTE is computed once into a temporary work table; inlined means it's folded into the main query and optimized together (often faster, since predicates can push down).

• PostgreSQL — inlined since v12 when referenced once and not recursive; before v12, CTEs were an optimization fence (always materialized). You can force either with MATERIALIZED / NOT MATERIALIZED.
• SQL Server / MySQL 8+ — generally inline CTEs into the plan.

-- Postgres: force materialization (an optimization barrier)
WITH t AS MATERIALIZED (SELECT * FROM big_table WHERE active)
SELECT * FROM t WHERE id = 5;

Rule of thumb: don't assume a CTE is materialized — check EXPLAIN; in modern Postgres use MATERIALIZED/NOT MATERIALIZED to control it.

Not inherently — CTEs are primarily a readability tool. In most engines a CTE produces the same plan as the equivalent subquery or derived table.

Caveats that can hurt or help:

• An older Postgres materializing a CTE could block predicate pushdown, making it slower than an inline subquery.
• A CTE referenced many times that the engine recomputes each time can be slower than expected — materializing it once may help.
• Recursive CTEs over deep hierarchies can be expensive regardless.

Rule of thumb: choose CTEs for clarity, not speed; verify with EXPLAIN and consider explicit materialization only when the plan shows a problem.

Yes. Put the WITH clause in front of the DML statement and reference the CTE in it — handy for staging the rows to modify.

-- delete all but the latest order per customer
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY customer_id
                                  ORDER BY created_at DESC) AS rn
    FROM orders
)
DELETE FROM orders
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Postgres even allows data-modifying CTEs (INSERT/UPDATE/DELETE ... RETURNING inside a WITH), letting one statement write to several tables.

Rule of thumb: use a CTE to compute exactly which rows a DML statement should touch — especially with window functions for "keep the latest" logic.

Window functions like ROW_NUMBER() and RANK() can't be used directly in WHERE (they're computed after filtering). A CTE (or derived table) lets you compute the window value first, then filter on it in the outer query.

-- top 3 earners per department
WITH ranked AS (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, dept_id, salary
FROM   ranked
WHERE  rn <= 3;       -- filter on the window result

Rule of thumb: compute the window function in a CTE, then filter its alias in the outer query — the standard "top-N-per-group" pattern.

A CTE is visible only within the single statement it's attached to. Once that statement finishes, the CTE is gone — you can't reference it from a later statement, and it isn't stored in the schema.

WITH t AS (SELECT 1 AS x)
SELECT * FROM t;     -- works

SELECT * FROM t;     -- ERROR: t no longer exists

Also, later CTEs in the same WITH can see earlier ones, but not vice versa (except a recursive CTE referencing itself).

Rule of thumb: a CTE lives and dies with one statement — for cross-statement reuse use a view or temp table.

Provide an explicit column list in parentheses after the CTE name. The CTE body's columns are mapped positionally to those names — useful when the body uses expressions or you want clearer names.

WITH sales (region, total) AS (
    SELECT region_id, SUM(amount)
    FROM   orders
    GROUP  BY region_id
)
SELECT region, total FROM sales ORDER BY total DESC;

The number of names must match the number of output columns. This is also the standard way to name the columns of a recursive CTE.

Rule of thumb: add (col1, col2, ...) after the CTE name to give its columns explicit names — required when the body's columns are unnamed expressions.

Both hold an intermediate result, but their lifetime and storage differ:

• A CTE is logical and scoped to one statement; it isn't physically stored (unless the engine materializes it internally) and can't be indexed.
• A temp table is a real table in tempdb/session scope, persists for the session/transaction, can be indexed, and is reusable across multiple statements.

-- temp table: reusable across statements, can index
CREATE TEMP TABLE big_spenders AS
    SELECT customer_id, SUM(total) s FROM orders GROUP BY customer_id;
CREATE INDEX ON big_spenders (s);
SELECT * FROM big_spenders WHERE s > 1000;

Rule of thumb: one-statement, no-index logic → CTE; reuse across statements or need an index on the intermediate → temp table.

Recursive CTEs shine wherever data is hierarchical or generated iteratively:

• Org charts / management chains — all reports under a manager.
• Category / comment trees — nested parent-child structures.
• Bill of materials — parts made of sub-parts.
• Graph traversal — shortest path, connected nodes.
• Generating series — number ranges or contiguous date sequences.

-- generate every date in a month
WITH RECURSIVE d AS (
    SELECT DATE '2026-06-01' AS day
    UNION ALL
    SELECT day + 1 FROM d WHERE day < DATE '2026-06-30'
)
SELECT day FROM d;

Rule of thumb: reach for a recursive CTE whenever you'd otherwise need a loop to walk a tree, graph, or generated series.

CTEs are great for clarity, but they aren't always the right tool:

• When you need the intermediate reused across multiple statements — a view or temp table fits better.
• When you need an index on the intermediate result — use a temp table.
• On older Postgres, when materialization would block predicate pushdown and slow the query — an inline subquery may be faster.
• For a trivial one-liner where a simple subquery is just as clear.

Rule of thumb: use CTEs for readable, single-statement logic; switch to views or temp tables when you need persistence, reuse, or indexing.