Mid LevelAdvanced SQL2 Interview QuestionsFREE

CTE and Recursive SQL Interview Questions

What Interviewers Are Testing

CTE interview questions test your ability to simplify complex queries with named temporary result sets and traverse hierarchical data using recursive CTEs.

Interview Questions & Model Answers

Q1

What is a CTE in SQL and how does it differ from a subquery?

A

A CTE (Common Table Expression) is a named temporary result set defined with the WITH keyword that makes queries more readable. Unlike subqueries, CTEs: can be referenced multiple times in the same query, can be recursive, and are defined at the top of the query making them easier to read and maintain. Performance-wise, CTEs are often equivalent to subqueries (not automatically faster), though PostgreSQL 12+ added MATERIALIZED keyword for explicit control.

Example
-- Subquery (harder to read)
SELECT d.name, sq.avg_sal
FROM departments d
JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) sq
  ON d.id = sq.dept_id;

-- CTE (cleaner)
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT d.name, da.avg_sal
FROM departments d
JOIN dept_avg da ON d.id = da.dept_id;
Try in Playground

Potential Follow-up Questions:

  • Are CTEs faster than subqueries?
  • When would you use a temp table over a CTE?
Q2

Write a recursive CTE to display an employee organizational hierarchy.

A

A recursive CTE has two parts: an anchor query (base case — top-level employees with no manager) and a recursive query (joins CTE output to table to add the next level). It iterates until no more rows are added.

Example
WITH RECURSIVE org_chart AS (
  -- Anchor: top-level (no manager)
  SELECT id, name, manager_id, 1 AS depth, name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: add direct reports
  SELECT e.id, e.name, e.manager_id,
         oc.depth + 1,
         oc.path || ' > ' || e.name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT depth, name, path
FROM org_chart
ORDER BY path;
Try in Playground

Potential Follow-up Questions:

  • How do you prevent infinite loops in recursive CTEs?
  • What is the CYCLE clause?

Frequently Asked Questions

When would you use a recursive CTE?
Use recursive CTEs for: organizational hierarchies (employee-manager chains), bill of materials (product components and sub-components), file system trees (folders and subfolders), category trees (e-commerce categories), and any graph traversal where you need to follow parent-child relationships through an unknown number of levels.
What is the difference between a CTE and a temporary table?
CTE: defined and available only for one SQL statement, not stored on disk, usually optimized the same as a subquery. Temporary table: physically created in tempdb/temp tablespace, persists for the session, can have indexes, can be referenced by multiple statements in the same session. Use a temp table when: you need to reference the results multiple times across different statements, the intermediate result is large and benefits from indexing, or you need to build the result incrementally.

Related Interview Topics

Window FunctionsSql JoinsSubqueriesQuery Optimization

Practice Answering Live

Use our Interview Arena to practice SQL challenges under real interview conditions. Free.

Open Interview Arena SQL Playground