Home / Glossary / CTE (Common Table Expression)

CTE (Common Table Expression)

Named temporary result set defined with WITH … AS (…).

CTEs make complex queries readable by naming intermediate results. They can be referenced multiple times in the same query and can be chained. They are the SQL equivalent of a named function.

WITH daily_ret AS (
  SELECT ticker, close / LAG(close) OVER (PARTITION BY ticker ORDER BY date) - 1 AS ret
  FROM equity_prices
)
SELECT * FROM daily_ret;