Common Table Expression (CTE) in SQL

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

What Is a Common Table Expression?

A common table expression is a named, temporary result set defined using the WITH keyword. It lives only for the single SQL statement that follows it. Think of it as giving a name to a subquery, except you declare it before the main query and can reference it as many times as you need.

In the corpus, we see all three names used interchangeably: “CTE” in 47% of transcripts, “WITH clause” in 31%, and the full “common table expression” in the remaining 22%. Interviewers won't care which one you use. They'll care whether you can name, reuse, and chain them without losing your place.

-- Basic common table expression
WITH active_users AS (
  SELECT user_id, name, email
  FROM users
  WHERE status = 'active'
    AND last_login >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  au.name,
  COUNT(o.order_id) AS recent_orders
FROM active_users au
LEFT JOIN orders o ON au.user_id = o.user_id
GROUP BY au.name
ORDER BY recent_orders DESC;

Common Table Expression Syntax

The WITH keyword introduces one or more CTEs. Each CTE has a name, an optional column list, and a SELECT statement wrapped in parentheses. Multiple CTEs are separated by commas after a single WITH keyword. The main query follows after the last CTE.

-- Syntax template
WITH cte_name [(column1, column2, ...)] AS (
  SELECT ...
)
SELECT ... FROM cte_name;

-- Multiple CTEs (comma-separated, one WITH)
WITH
  step1 AS (
    SELECT ...
  ),
  step2 AS (
    SELECT ... FROM step1
  ),
  step3 AS (
    SELECT ... FROM step2
  )
SELECT * FROM step3;

Why Use a Common Table Expression?

Common Table Expression Patterns

WITH recent_orders AS (
  SELECT user_id, amount
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM recent_orders
GROUP BY user_id
ORDER BY total_spent DESC;

WITH dept_salaries AS (
  SELECT
    department,
    name,
    salary
  FROM employees
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS rn
  FROM dept_salaries
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3;

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  curr.month,
  curr.revenue AS current_revenue,
  prev.revenue AS previous_revenue,
  curr.revenue - prev.revenue AS mom_change
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
  ON curr.month = prev.month + INTERVAL '1 month'
ORDER BY curr.month;

WITH RECURSIVE org_tree AS (
  -- Anchor: CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 20
)
SELECT * FROM org_tree ORDER BY depth, name;

CTE vs. Subquery vs. Temp Table

Performance Considerations

In PostgreSQL 12+, BigQuery, Snowflake, and Trino, the optimizer can inline CTEs, making them identical in performance to the equivalent subquery. In PostgreSQL 11 and earlier, CTEs were always materialized (optimization fences), which prevented predicate pushdown and could hurt performance.

-- PostgreSQL 12+: force materialization
WITH expensive_calc AS MATERIALIZED (
  SELECT user_id, heavy_computation(data) AS result
  FROM big_table
)
SELECT * FROM expensive_calc WHERE result > 100;

-- PostgreSQL 12+: force inlining
WITH filtered AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM filtered WHERE amount > 1000;

Common Table Expression FAQ

Related Guides