Tier 3 · Intermediate

Common Table Expressions (CTEs) in SQL

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Before this Derived Tables (Subqueries in FROM)

Builds toward Chained CTEs, Temp Tables and CREATE TABLE AS SELECT, generate_series() for Sequences and Date Spines, Query Structure Patterns for Performance

What are CTEs in SQL?

A CTE lets you name a subquery and put it at the top of your query, where you can read it before anything else runs.

You're writing a monthly revenue report. The query needs to aggregate orders into monthly totals and then filter to show only months above a threshold. Without a CTE, you'd nest the aggregation inside a FROM clause as an anonymous subquery — readable in isolation, but buried inside the query. With a CTE, you name it monthly_revenue, define it at the top, and reference it by name in the main query below. The logic reads in the order it executes.

How do you write a WITH clause in SQL?

The WITH keyword opens the definition. After it comes the name, then AS, then the subquery in parentheses:

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', ordered_at) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue
FROM monthly_revenue
WHERE revenue > 10000
ORDER BY month

The CTE computes the monthly aggregation. The main query filters it. Each layer does one thing, and you can read either in isolation. The CTE name behaves exactly like a table reference in the main query — you can join against it, filter it, aggregate it, or use it in a WHERE clause.

How long does a CTE name last in SQL?

One rule: the CTE name only exists within this one statement. You cannot reference it in a later query, even in the same session. Each query starts fresh.

When is a CTE better than a subquery?

The biggest advantage over a plain subquery appears when you need the same intermediate result in more than one place. A subquery in FROM has no name — you'd have to copy the entire thing wherever you need it. A CTE is defined once and can be referenced anywhere in the main query by name. You can also use it to test intermediate logic: comment out the main SELECT, add SELECT * FROM monthly_revenue, and run it. You get the intermediate result directly, without executing the rest.

WITH us_orders AS (
  SELECT o.id, o.total_amount, c.name AS customer_name
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  WHERE c.country = 'US'
)
SELECT customer_name, SUM(total_amount) AS total_spent
FROM us_orders
GROUP BY customer_name
ORDER BY total_spent DESC

Are CTEs slower than subqueries in PostgreSQL?

The one thing that trips people up: a CTE is not automatically faster than a derived table.

In PostgreSQL 12 and later, CTEs are inlined by default — the planner treats them as named subqueries and can optimize across the boundary. In older PostgreSQL versions, CTEs were always materialized: executed once, result stored, planner could not push filters inside. If you're on PostgreSQL 11 or earlier, a heavily filtered CTE can block optimizations that matter for large tables. For modern PostgreSQL, the choice between a CTE and a derived table is mostly a readability decision.

Check your understanding

A CTE is defined with WITH my_data AS (...). Where can you reference the name my_data?

Practice CTEs in SQL

Practice · easy ecommerce · Brightlane

Brightlane's operations team maintains a daily pipeline log showing how many orders sit in each stage.

Write a query to return the count of orders in each status.

Assumptions:

The orders table has one row per order with a status.
Each status value present in orders should appear once in the report.

Output:

One row per status, with columns status and order_count.

Schema · ecommerce5 tables? = nullable

9 CTEs practice problems

Write a query to return the count of orders in each status.

easy ecommerce

Write a query to return the number of active employees in each department.

easy hr

Write a query to return each user's ID and the number of sessions they have recorded.

easy analytics

Write a query to return the category ID and average price for every category meeting that threshold.

medium ecommerce

Write a query to return the ID and session count of every user who has recorded more than 5 sessions.

medium analytics

Write a query to return the status, order count, and total revenue for every status whose combined order revenue exceeds $5,000.

medium ecommerce

Write a query to return the department ID and employee count for every department that has 3 or more employees on record.

medium hr

Write a query to return the user ID and session count for every user whose session count is above the per-user average.

hard analytics

Write a query to return the category ID and average price for every assigned category whose average product price exceeds $500.

hard ecommerce

Start learning to practice all 9 CTEs problems, with instant grading and mastery tracking.

Common questions about CTEs

Can a CTE be referenced more than once in the same query?

Yes, and that is one of its advantages over a subquery in FROM. The definition is written once and named, so both references read the same intermediate result instead of repeating the whole thing twice.

Can a CTE refer to itself?

Only if you write WITH RECURSIVE. A plain CTE cannot see its own name, and trying gives an error saying the relation does not exist. The keyword is what enables the repeated evaluation that walks a hierarchy.

Do you have to use every CTE you define?

No. An unreferenced CTE is legal and the query runs normally, which is handy while you are building one up. It also means a typo in the main query can leave a CTE quietly orphaned, so check that the names you defined are the names you read from.