All 40 lessons

Lesson 23 of 40 · Subqueries & CTEs

SQL CTE (WITH Clause)

By Skillancy Editorial Team

Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.

A CTE, or Common Table Expression, is a named temporary result you define at the top of a query with WITH. It lets you break a complicated query into readable steps: first calculate this, then that, then combine them. Most analysts consider CTEs the single biggest readability upgrade in SQL.

Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT ...
FROM cte_name;

Multiple CTEs are separated by commas, and each can use the ones before it:

WITH step_1 AS (...),
     step_2 AS (SELECT ... FROM step_1)
SELECT ... FROM step_2;

Examples

1. A single CTE

Net revenue per delivered order, then the average order value:

WITH order_revenue AS (
  SELECT o.order_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY o.order_id
)
SELECT COUNT(*) AS delivered_orders,
       ROUND(AVG(revenue), 2) AS avg_order_value,
       ROUND(MAX(revenue), 2) AS biggest_order
FROM order_revenue;
Try it in the SQL Compiler

Result:

delivered_ordersavg_order_valuebiggest_order
167612.3628838.50

(1 row)

Read it top to bottom: build one row per order, then summarise those rows.

2. Chained CTEs: customer segments

WITH customer_spend AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS total_spend
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY o.customer_id
),
segmented AS (
  SELECT customer_id,
         total_spend,
         CASE
           WHEN total_spend >= 25000 THEN 'High value'
           WHEN total_spend >= 5000  THEN 'Medium value'
           ELSE 'Low value'
         END AS segment
  FROM customer_spend
)
SELECT segment,
       COUNT(*) AS customers,
       ROUND(SUM(total_spend), 2) AS revenue
FROM segmented
GROUP BY segment
ORDER BY revenue DESC;
Try it in the SQL Compiler

Result:

segmentcustomersrevenue
High value261774.80
Medium value449606.70
Low value410416.25

(3 rows)

Each step has one job and a clear name, which makes the query easy to check and change.

3. Using a CTE twice

A CTE can be referenced more than once. Compare each category's revenue with the overall average category revenue:

WITH category_revenue AS (
  SELECT cat.category_name,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
  FROM order_items oi
  JOIN orders o       ON o.order_id = oi.order_id
  JOIN products p     ON p.product_id = oi.product_id
  JOIN categories cat ON cat.category_id = p.category_id
  WHERE o.status = 'Delivered'
  GROUP BY cat.category_name
)
SELECT category_name,
       ROUND(revenue, 2) AS revenue,
       ROUND(revenue / (SELECT SUM(revenue) FROM category_revenue) * 100, 1) AS pct_of_total
FROM category_revenue
WHERE revenue > (SELECT AVG(revenue) FROM category_revenue)
ORDER BY revenue DESC;
Try it in the SQL Compiler

Result:

category_namerevenuepct_of_total
Electronics79383.6565.2

(1 row)

4. Fixing double counting with CTEs

From the multiple joins lesson: aggregate each table to the same level first, then join. Orders and units per customer, without inflation:

WITH orders_per_customer AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  GROUP BY customer_id
),
units_per_customer AS (
  SELECT o.customer_id, SUM(oi.quantity) AS units
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id
)
SELECT c.customer_name,
       COALESCE(opc.orders, 0) AS orders,
       COALESCE(upc.units, 0) AS units
FROM customers c
LEFT JOIN orders_per_customer opc ON opc.customer_id = c.customer_id
LEFT JOIN units_per_customer upc  ON upc.customer_id = c.customer_id
ORDER BY orders DESC, c.customer_name
LIMIT 5;
Try it in the SQL Compiler

Result:

customer_nameordersunits
Aarav Sharma412
Rohan Gupta310
Sneha Reddy312
Ananya Iyer23
Arjun Patel23

(5 rows)

CTE vs subquery

CTESubquery
Readabilitytop to bottom, named stepsnested inside out
Reuse in one queryyesno (repeat it)
Recursionyes (WITH RECURSIVE)no
Performanceusually the sameusually the same

Common mistakes

  • A comma after the last CTE. WITH a AS (...), SELECT ... is a syntax error.
  • Expecting a CTE to persist. It exists only for the statement it's attached to. Use a view to save it.
  • Forgetting WITH once. Only the first CTE starts with WITH; the rest just follow a comma.

Practice question

Using CTEs, find the month (in 2025) with the highest number of delivered orders.

Show solution
WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month,
         COUNT(*) AS delivered_orders
  FROM orders
  WHERE status = 'Delivered'
    AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
  GROUP BY 1
)
SELECT month, delivered_orders
FROM monthly
WHERE delivered_orders = (SELECT MAX(delivered_orders) FROM monthly)
ORDER BY month;
Try it in the SQL Compiler

Result:

monthdelivered_orders
2025-01-012
2025-05-012
2025-06-012
2025-08-012
2025-10-012

(5 rows)

Several months tie, which is why comparing to MAX is safer than LIMIT 1. (GROUP BY 1 means "group by the first selected column".)

Key takeaways

  • WITH name AS (...) creates a named temporary result for one query.
  • Chain CTEs with commas to build a query in clear steps.
  • A CTE can be referenced several times in the main query.
  • Use CTEs to pre-aggregate before joining and avoid double counting.
Want every variation on one page?CTE in SQL: the complete guide

Frequently asked questions

What is a CTE in SQL?

A Common Table Expression is a named, temporary result defined with WITH name AS (SELECT ...). The main query can then read from it like a table. It exists only for that one statement.

What is the difference between a CTE and a subquery?

They can express the same logic. A CTE is named and defined up front, so it reads top to bottom, can be referenced several times, and can be recursive. Subqueries are nested inside the query.

Is a CTE faster than a subquery?

Usually the performance is the same. Since PostgreSQL 12, simple CTEs are inlined like subqueries. Use CTEs for readability, not speed.

← PreviousCorrelated Subqueries and EXISTSNext →SQL Recursive CTE
Newsletter

Keep learning SQL

One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.

Prefer a one-page summary? Download the free SQL cheat sheet (PDF)