Subqueries & CTEs · SQL & Analytical Warehousing

Overview

A subquery is a query in parentheses. WITH gives it a name so the next step can read it.

On this page7 sections

1What you will do
2Why this skill
3How the code works
4Worked examples
5Common beginner questions
6What comes next
7Practice

What you will do

As your queries grow more complex, nesting subqueries inside each other becomes difficult to read and debug. A Common Table Expression (CTE) solves this by letting you name intermediate results. Think of a CTE as a temporary, named 'mini-table' that exists only for the duration of your query.

You write a CTE using the WITH keyword: WITH name AS (SELECT ...). The name becomes available in subsequent parts of the query as if it were a real table. You can chain multiple CTEs separated by commas, and later CTEs can reference earlier ones.

A subquery is similar: a SELECT statement inside parentheses placed in a FROM clause. The difference is readability. Subqueries nest inward, making deep logic hard to follow. CTEs read top-to-bottom like a recipe, with each step clearly named.

Why this skill

Imagine you need to answer: 'For paid orders, what is the total quantity of items, and how does each order compare to the average paid order?' This requires multiple steps: filter paid orders, aggregate items, calculate an average, and compare. Without CTEs, this becomes deeply nested and nearly impossible to debug.

Data engineers build multi-step transformations for production pipelines. Each step (filter, aggregate, join, clean) should be independently testable. CTEs let you run just one stage to verify it is correct before moving to the next. Named stages also make code reviews faster because reviewers can understand each piece in isolation.

How the code works

Source data

Starting data: five orders with different statuses.

order_id	order_status	order_total
O0001	paid	149.00
O0002	cancelled	42.50
O0003	paid	88.00
O0004	pending	210.75
O0005	paid	55.20

Worked examples

Example 1: Simple subquery vs CTE

A subquery in FROM creates a temporary result. For one quick transformation, this compact form works well. But notice how you must read inward (inside the parentheses) to understand what the inner query does:

Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.

SQLSubquery form: the inner logic is hidden inside parentheses

SELECT paid_orders, gmv
FROM (
  SELECT
    COUNT(*) AS paid_orders,
    SUM(order_total) AS gmv
  FROM orders
  WHERE order_status = 'paid'
) AS paid_summary;

Result: one summary row from the nested subquery.

paid_orders	gmv
3	292.20

Now the same logic as a CTE. Notice how the name 'paid_summary' appears at the top, making it immediately clear what is being computed:

SQLCTE form: the name appears first, reading top-to-bottom

WITH paid_summary AS (
  SELECT
    COUNT(*) AS paid_orders,
    SUM(order_total) AS gmv
  FROM orders
  WHERE order_status = 'paid'
)
SELECT paid_orders, gmv
FROM paid_summary;

Same result, but the query reads like a named step.

paid_orders	gmv
3	292.20

Subquery nests inward, CTE reads downward

CTEs flow top-to-bottom like a recipe. Subqueries hide logic inside parentheses.

Example 2: Multi-stage pipeline with two CTEs

Real pipelines need multiple steps. You can chain CTEs with commas. Each CTE can reference the ones defined before it.

SQLTwo CTEs: filter paid orders and aggregate items, then join

WITH paid AS (
  SELECT order_id, order_total
  FROM orders
  WHERE order_status = 'paid'
),
item_totals AS (
  SELECT
    order_id,
    SUM(quantity) AS units
  FROM order_items
  GROUP BY order_id
)
SELECT
  p.order_id,
  p.order_total,
  i.units
FROM paid AS p
LEFT JOIN item_totals AS i
  ON i.order_id = p.order_id
ORDER BY p.order_total DESC
LIMIT 10;

Result: paid orders joined with their item quantities. O0005 has NULL units because it had no items.

order_id	order_total	units
O0001	149.00	3
O0003	88.00	2
O0005	55.20	NULL

CTE dependency graph

Independent CTEs prepared from different tables meet only at the final join.

Example 3: Common mistake - trivial CTEs

Do not create a CTE for every single operation. A CTE that does nothing more than rename a table adds noise without value:

SQLOnly create CTEs when they add meaningful transformation

-- AVOID: trivial CTE that just renames a table
WITH my_orders AS (
  SELECT * FROM orders
)
SELECT * FROM my_orders;

-- BETTER: use the table directly if no transformation is needed
SELECT order_id, order_total
FROM orders
WHERE order_status = 'paid';

Name the grain and meaning

Name your CTEs after their business meaning and grain. Prefer item_totals_by_order over cte2 or temp. The name should tell the next reader what was measured and at what level.

CTEs are not cached by default

Most database engines inline CTEs into the execution plan. A CTE referenced twice might be computed twice. Do not assume a CTE is materialized. Use EXPLAIN to check actual behavior.

Readable does not mean correct

A well-named CTE can still have bugs: wrong filters, duplicate keys, or missing rows. Always validate the row grain and key uniqueness at each stage, especially after aggregations.

Choose the simplest form that keeps the transformation understandable.

Form	Best fit	Advantage	Disadvantage
Flat query	Single direct operation	Least syntax	Gets messy with multiple concerns
Subquery	One local derived table	Keeps transformation near use	Deep nesting is hard to read
CTE	Several named stages	Readable flow, easy to debug	Can add unnecessary stages
View	Shared reusable logic	Governed, persistent	Requires DDL permissions

Copy-paste without reading the output

Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.

Common beginner questions

Can a CTE reference another CTE?

Yes, but only ones defined earlier in the WITH block. CTEs are processed in order, top to bottom. A later CTE can use an earlier one as its source.

Is a CTE faster than a subquery?

Usually no. Most engines treat them the same way internally. The benefit is readability and maintainability, not performance. Always use EXPLAIN to verify.

Can I use a CTE in an INSERT or UPDATE?

Yes. WITH ... INSERT INTO ... SELECT FROM cte_name is valid. This is common in production pipelines that stage data before loading.

What comes next

CTEs let you structure multi-step queries. The next lesson introduces window functions, which let you compute rankings, running totals, and comparisons without collapsing rows. This is the technique that makes deduplication and sequencing possible.

Practice

Run Sample for a two-CTE pipeline. Then complete the exercise: write a CTE named paid that selects order_id and order_total from orders where order_status is 'paid'. Then SELECT from it with LIMIT 10.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:

Was this useful?