Name A Subquery With A CTE

Use `WITH` to define a named temporary result set that can be referenced in the main query, improving readability over inline subqueries.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Summarize order totals per customer, then filter top spenders

The CTE totals groups and sums each customer's orders. The outer query then filters to customers whose total exceeds 200. Bob (140) is excluded. Writing this without a CTE would require a subquery in FROM or repeating the aggregation twice.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), amount INT);

INSERT INTO
  orders (id, customer, amount)
VALUES
  (1, 'Alice', 100),
  (2, 'Alice', 150),
  (3, 'Bob', 80),
  (4, 'Bob', 60),
  (5, 'Carol', 300);

Validated query Shared across supported engines.

SQL

WITH
  totals AS (
    SELECT
      customer,
      SUM(amount) AS total
    FROM
      orders
    GROUP BY
      customer
  )
SELECT
  customer,
  total
FROM
  totals
WHERE
  total > 200
ORDER BY
  total DESC;

Expected result Returned rows for the shared example.

customer	total
Carol	300
Alice	250

Output is identical across all supported engines.

Useful when

Where this command helps.

making a multi-step query easier to read
reusing a derived result within one larger statement

Explanation

What the command is doing.

A Common Table Expression (CTE) is defined with WITH name AS (SELECT ...) before the main query. It behaves like an inline view: the database evaluates it and makes it available by name for the rest of the query. CTEs are especially useful when the same derived result is referenced multiple times, or when breaking a complex query into readable steps.

Name A Subquery With A CTE

Summarize order totals per customer, then filter top spenders

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.