Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Common Table Expressions
This device
Course contentsCommon Table Expressions · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONWindow Functions
UP NEXTINNER, LEFT, RIGHT, and FULL Joins · Planned
Reports, aggregation, and analytics · Lesson 12 135 min

Common Table Expressions

A common table expression, or CTE, names a query result for use by the statement that follows. WITH lets you express a report as reviewable stages; WITH RECURSIVE lets a stage produce more rows from rows it already found.

What you will leave with

You will write single and chained CTEs, inspect an intermediate step, filter a computed window result, reuse a named result, and bound recursive queries over a sequence and a reporting hierarchy.

Name each transformation

SourceChoose the rows the report means to include.
StageGive an intermediate result a useful name.
InspectRun a stage alone to check its rows and columns.
FinishApply the final filter, projection, and order.
SOURCE8 orderspaid · pending · refunded

Only five orders are paid.

CTEpaid_orders5 rows

A named result holds the filtered population for this statement.

FINAL12,000 centsSUM(amount_cents)

The outer SELECT reads the named result.

WITH names a result inside one statement

The paid_orders CTE contains five paid rows. The following SELECT reads it like a table and returns a count of five and a 12,000-cent sum. Its name exists only for this statement; it does not create a permanent table or view.

SQL BROWSER RUNNER

Build a paid-orders CTE

Name a filtered result and summarize it in the outer SELECT.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

WITH paid_orders AS (
  SELECT order_id, customer_id, amount_cents
  FROM orders WHERE status = 'paid'
)
SELECT COUNT(*) AS paid_orders, SUM(amount_cents) AS paid_cents
FROM paid_orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the outer SELECT to AVG(amount_cents) and predict 2,400 cents.

Inspect an intermediate step before trusting the report

Temporarily select from the CTE itself to verify its grain and population. This version shows five paid order IDs. That check is especially useful before adding aggregates, rankings, or a second CTE.

SQL BROWSER RUNNER

Inspect the named rows

Run the intermediate paid-orders result directly.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

WITH paid_orders AS (
  SELECT order_id, customer_id, amount_cents
  FROM orders WHERE status = 'paid'
)
SELECT * FROM paid_orders ORDER BY order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Confirm that neither pending nor refunded orders appear.

Chain CTEs for a multi-step report

Later CTEs can read earlier ones. Here paid_orders filters first, customer_totals groups second, and the final SELECT keeps customers with at least 3,000 paid cents. Customers 10 and 12 remain. Keep each stage focused on one question.

SQL BROWSER RUNNER

Filter, group, then publish

Read a prior CTE in a second named stage.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

WITH paid_orders AS (
  SELECT customer_id, amount_cents FROM orders WHERE status = 'paid'
), customer_totals AS (
  SELECT customer_id, COUNT(*) AS paid_orders, SUM(amount_cents) AS paid_cents
  FROM paid_orders GROUP BY customer_id
)
SELECT customer_id, paid_orders, paid_cents
FROM customer_totals WHERE paid_cents >= 3000 ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Remove the final threshold and verify all four paying customers.

An optional column list makes the shape explicit

WITH customer_totals(customer_id, paid_cents) AS (...) gives the CTE output columns explicit names. The number of listed names must match the number of selected expressions. This is helpful when an expression would otherwise have a database-generated label.

SQL BROWSER RUNNER

Name a CTE output shape

Declare two result-column names beside the CTE name.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

WITH customer_totals(customer_id, paid_cents) AS (
  SELECT customer_id, SUM(amount_cents)
  FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT customer_id, paid_cents FROM customer_totals ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Rename paid_cents to revenue_cents in both the CTE list and outer SELECT.

Filter a window result in an outer stage

The previous lesson used ROW_NUMBER() to rank rows. A base WHERE cannot filter that computed position in the same SELECT. Put the window expression in a CTE, then apply WHERE position = 1 outside it. The result is the largest paid order per customer, with order ID breaking ties.

SQL BROWSER RUNNER

Keep each customer's top paid order

Compute row numbers first, then filter them outside the CTE.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

WITH ranked_paid AS (
  SELECT order_id, customer_id, amount_cents,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY amount_cents DESC, order_id
         ) AS position
  FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, amount_cents
FROM ranked_paid WHERE position = 1 ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change position = 1 to position <= 2 and inspect customer 10.

Reuse a named result where it clarifies the metric

The final SELECT reads customer_totals as its row source and in a scalar subquery for the average customer total. That average is 3,000 cents across four customers—not the 2,400-cent average order amount. The metric changes because its unit is a customer rather than an order. Whether a database recomputes or materializes a CTE is engine- and plan-dependent; a CTE is not automatically a performance optimization.

SQL BROWSER RUNNER

Compare each customer with the customer average

Reference one named stage from the outer query and a subquery.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

WITH paid_orders AS (
  SELECT customer_id, amount_cents FROM orders WHERE status = 'paid'
), customer_totals AS (
  SELECT customer_id, SUM(amount_cents) AS paid_cents
  FROM paid_orders GROUP BY customer_id
)
SELECT customer_id, paid_cents,
       (SELECT ROUND(AVG(paid_cents), 2) FROM customer_totals) AS customer_average_cents
FROM customer_totals ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Explain why customer 10's 4,000 cents is above the 3,000-cent customer average.

Recursive CTEs need a seed and a stopping rule

WITH RECURSIVE starts with an anchor query, then repeatedly runs a recursive member against the rows found so far. UNION ALL appends each wave. In the number example, one is the anchor and n < 5 prevents expansion after five. The outer ORDER BY controls display order.

SQL BROWSER RUNNER

Generate a bounded sequence

Observe the anchor, recursive step, and termination condition.

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers ORDER BY n;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the bound to seven and predict the final row.

Traverse a reporting hierarchy

The hierarchy query anchors at Bilal, employee 2. Each recursive step finds employees whose manager_id matches a previously found employee. It returns Bilal at depth zero, Dina and Elias at depth one, and Farah at depth two. The depth guard is a safety bound, not cycle detection. Real mutable graphs need an explicit cycle-prevention strategy; a bad cycle could otherwise revisit nodes.

SQL BROWSER RUNNER

Walk a manager's team

Traverse a six-person employee hierarchy with a bounded depth.

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
  (1, 'Amina', NULL),
  (2, 'Bilal', 1),
  (3, 'Celia', 1),
  (4, 'Dina', 2),
  (5, 'Elias', 2),
  (6, 'Farah', 4);

WITH RECURSIVE team(employee_id, employee_name, manager_id, depth) AS (
  SELECT employee_id, employee_name, manager_id, 0
  FROM employees WHERE employee_id = 2
  UNION ALL
  SELECT e.employee_id, e.employee_name, e.manager_id, team.depth + 1
  FROM employees AS e
  JOIN team ON e.manager_id = team.employee_id
  WHERE team.depth < 10
)
SELECT employee_id, employee_name, depth
FROM team ORDER BY depth, employee_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Anchor at Amina instead and predict all six employees and their depths.

Common CTE mistakes

WITH means saved table

Wrong lifetime

A CTE name is scoped to one statement. Use a table or view for durable reuse.

CTE means faster

Unproven plan

Execution and materialization vary by engine and optimizer. Measure performance.

Recursive step without bound

Runaway expansion

Give recursion a terminating condition and guard against cycles in graph data.

Implicit row order

Unstable presentation

Use an outer ORDER BY when the final result needs an order.

Independent lab: top paid order and customer total

Build a report with each paying customer's highest-value order and total paid cents. The starter uses paid_orders to establish the population and ranked to compute position and customer total. It returns four customers: 10 and 12 tie on 4,000-cent totals, followed by 11 at 2,500 and 13 at 1,500. The outer ORDER BY breaks the total tie by customer ID.

SQL BROWSER RUNNER

Audit a staged customer report

Filter paid rows, calculate windows, then publish one result per customer.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

-- Highest-value paid order per customer, with each customer's total.
WITH paid_orders AS (
  SELECT order_id, customer_id, amount_cents
  FROM orders WHERE status = 'paid'
), ranked AS (
  SELECT order_id, customer_id, amount_cents,
         SUM(amount_cents) OVER (PARTITION BY customer_id) AS customer_cents,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY amount_cents DESC, order_id
         ) AS position
  FROM paid_orders
)
SELECT customer_id, order_id, amount_cents, customer_cents
FROM ranked WHERE position = 1
ORDER BY customer_cents DESC, customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify all four rows. Add a final WHERE condition for customer_cents >= 3000 and predict the survivors.

Lab review criteria

Only paid orders enter the first CTE. The customer total is computed before the outer position filter, so it includes all of that customer's paid orders. The rank tie-breaker and final ORDER BY are deterministic.

Lesson review

CTEs give a complex statement named, inspectable stages. Use ordinary CTEs to make transformations easier to reason about, and recursive CTEs when rows must lead to more rows. Check scope, grain, termination, cycles, and final ordering instead of assuming a CTE handles them automatically.

  • I can write and inspect a single named result.
  • I can chain CTEs without losing the intended population.
  • I can filter a computed window value outside its CTE.
  • I can identify an anchor, recursive member, and stop condition.
  • I know CTE readability does not guarantee faster execution.
KNOWLEDGE CHECK

Check CTE reasoning

Answer all ten questions, then revisit any example whose scope or recursion surprised you.

01How long is a CTE name available?
02What can a second CTE in the same WITH clause read?
03Why select directly from an intermediate CTE while developing?
04Where should position = 1 be filtered after ROW_NUMBER() calculates position?
05What is the anchor of WITH RECURSIVE numbers(n) starting at SELECT 1?
06What stops the number CTE from producing rows after five?
07Why is a depth < 10 guard not complete cycle detection?
08Does writing a CTE guarantee materialization or faster execution?
09Why is the average customer total different from the average paid order?
10What ensures the final CTE report appears in a predictable order?
PREVIOUS LESSONWindow Functions
UP NEXTINNER, LEFT, RIGHT, and FULL Joins · Planned
ON THIS PAGECommon Table ExpressionsLesson mapWITHInspect a stepChained CTEsColumn namesWindow filterReuseRecursionHierarchyCommon mistakesIndependent labLesson reviewKnowledge check
Course contents