What is a Common Table Expression (CTE), and when would you use it?

A Common Table Expression (CTE) is a named, temporary result set defined within a WITH clause, which can then be referenced multiple times within the main SQL query. It acts like a temporary view that exists only for the duration of that single query execution.

Why Use CTEs?

CTEs significantly enhance SQL query readability and maintainability by breaking down complex logic into smaller, more manageable, and named steps. This modularity makes queries easier to understand, debug, and refactor compared to deeply nested subqueries. They also promote reusability, allowing you to define a complex sub-query once and reference its result set multiple times without re-writing the logic. A powerful application is handling hierarchical or recursive data, such as organizational charts or bills of materials, where a CTE can repeatedly reference itself until a base condition is met.

WITH
  daily_sales AS (
    SELECT
      sale_date,
      SUM(amount) AS total_amount
    FROM sales_transactions
    WHERE
      sale_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY
      1
  ),
  avg_sales AS (
    SELECT
      AVG(total_amount) AS thirty_day_avg
    FROM daily_sales
  )
SELECT
  ds.sale_date,
  ds.total_amount,
  ROUND(ds.total_amount / asa.thirty_day_avg, 2) AS ratio_to_avg
FROM daily_sales AS ds, avg_sales AS asa;

Performance Considerations

The performance implications of CTEs vary significantly across database engines. In systems like PostgreSQL or SQL Server, CTEs are often treated as "optimization fences," meaning their results are materialized (computed and stored in a temporary space) once, even if referenced multiple times. This can be beneficial for complex, frequently reused logic but might hinder performance if the main query applies heavy filtering after the CTE's materialization.

Conversely, in modern analytical engines like Snowflake, BigQuery, or Spark SQL, CTEs are typically inlined by the optimizer. This means the CTE's logic is merged directly into the main query, allowing for more aggressive optimizations like predicate pushdown and column pruning. While this generally leads to better performance, it implies the CTE's logic might be re-evaluated if referenced multiple times without explicit caching or hints. Recursive CTEs, regardless of the engine, can be computationally expensive and require careful design to ensure termination and limit depth.

In the interview, also mention how CTEs are fundamental building blocks in data transformation frameworks like dbt, where they define intermediate steps in a modular and readable way.