Common Table Expressions (CTEs)

A CTE names a subquery in a WITH clause—readable multi-step SQL. Recursive CTEs traverse hierarchies (org charts, comment threads).

Basic CTE

WITH big_spenders AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 100
)
SELECT c.name, b.spent
FROM big_spenders b
JOIN customers c ON c.id = b.customer_id;

Practice: Advanced features vary by engine—SQLite 3.25+ supports window functions; test your version with a simple query.

Chained CTEs

WITH monthly AS (
  SELECT strftime('%Y-%m', ordered_at) AS ym,
         SUM(total) AS revenue
  FROM orders
  GROUP BY ym
),
ranked AS (
  SELECT ym, revenue,
         RANK() OVER (ORDER BY revenue DESC) AS rnk
  FROM monthly
)
SELECT * FROM ranked WHERE rnk <= 3;

Break complex reports into named stages—easier to debug than nested subqueries.

Recursive CTE sketch

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;

Recursive members reference the CTE name—useful for trees and graphs with careful cycle guards.

Important interview questions and answers

1. Q: CTE vs subquery?
   A: Same expressiveness for many cases; CTEs improve readability and can be referenced multiple times.
2. Q: Recursive CTE danger?
   A: Missing termination condition causes infinite recursion until engine limits hit.

Self-check

1. Which keyword introduces a CTE?
2. Why chain multiple CTEs in one WITH clause?

Tip: Name intermediate steps in WITH clauses—interviewers reward readable multi-step SQL.