SQL · Intermediate

SQL CTE (WITH clause): write readable queries

12 min read

Why it matters

Real analysis queries are 50 to 200 lines long, and the person reviewing yours (or you in three months) has to follow them. The SQL CTE, the WITH clause, is how you split a long query into named steps that read top to bottom like a recipe. SQL is in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and in live SQL interviews the candidates who talk through a CTE plan before typing usually finish; the ones who write one giant nested query often get lost in their own brackets.

The concept

WITH syntax

sql

WITH step_one AS (
    SELECT ...
),
step_two AS (
    SELECT ... FROM step_one      -- a later CTE can read an earlier one
)
SELECT ... FROM step_two;

One WITH, then CTEs separated by commas. No comma after the last one.
Each CTE can use any CTE defined above it, as many times as it likes.
A CTE lives only for that one statement.

One idea per CTE

A good CTE pipeline reads like the steps you would explain to a colleague:

Clean: filter out rows that are not what you are measuring, and say how many went.
Shape: join and aggregate to the grain you need (one row per country, per month).
Present: ranking, percentages, rounding and ordering, last.

Name each CTE for what it holds (clean_lines, monthly_revenue), not what it does (step2, temp).

Debugging a CTE

Because every step has a name, you can check any step on its own: keep the WITH block, and replace the final SELECT with SELECT * FROM the_step_you_doubt. Check row counts and a few values at each step before you trust the next one.

CTE vs subquery vs temp table vs view

	Lives for	Reusable in the query	Indexed	Use when
Subquery	One spot in one statement	No	No	Small, used once
CTE	One statement	Yes, by name	No	Long queries, readability
Temp table	The session	Yes, across statements	You can add indexes	A heavy step reused by several queries, or you need to inspect it
View	Until dropped	Yes, by everyone	No (the query re-runs)	Logic other people and tools should share

Materialisation: is the CTE run once or merged?

A database can treat a CTE in two ways:

Inline it, as if you had written a subquery, so filters from the outer query reach the tables underneath and indexes get used.
Materialise it: run it once, store the result, and read from that. Good when it is expensive and used several times; bad when the outer query only needs a few rows.

PostgreSQL 12+ inlines a CTE that is used once and has no side effects, and materialises it when it is used more than once. You can force either with AS MATERIALIZED or AS NOT MATERIALIZED. Before version 12 every CTE was materialised, which is why older blog posts call CTEs slow. SQL Server and most warehouses inline CTEs, so a CTE referenced three times may run three times there; if it is expensive, use a temp table.

A team style guide

Keywords in one case (upper or lower), and the same everywhere.
One column per line in long SELECTs; leading or trailing commas, but consistent.
Table aliases that mean something (c for customers is fine, t1 and t2 are not).
A comment wherever a filter encodes a business rule: -- exclude postage and fees.
GROUP BY and ORDER BY column names, not numbers, in code that will live.

Worked example: country revenue for a UK gift shop

The data is the UCI Online Retail export (541,909 lines, December 2010 to December 2011, CC BY 4.0), queried with DuckDB from Python. Step 5 uses PostgreSQL 16 and the invoice_lines table built in SQL INSERT, UPDATE and DELETE without breaking data. Every output is from a real run.

Setup

python

import duckdb

con = duckdb.connect()
con.sql("""
    CREATE OR REPLACE TABLE retail AS
    SELECT * FROM read_csv('online-retail-dataset.csv', header = true,
                           timestampformat = '%m/%d/%Y %H:%M')
""")

Download the CSV from the Spark Definitive Guide repository, then run each query with con.sql("...").show().

Step 1: the cleaning step, and what it removed

Revenue should count only real product sales. Before writing the analysis, measure what each cleaning rule takes out:

sql

WITH clean_lines AS (
    SELECT InvoiceNo, StockCode, Quantity, UnitPrice, InvoiceDate, CustomerID, Country,
           Quantity * UnitPrice AS line_revenue
    FROM retail
    WHERE InvoiceNo NOT LIKE 'C%'                 -- cancellations
      AND Quantity > 0
      AND UnitPrice > 0                           -- adjustments and free items
      AND regexp_matches(StockCode, '^[0-9]{5}')  -- real products, not postage or fees
)
SELECT (SELECT COUNT(*) FROM retail) AS raw_lines,
       (SELECT COUNT(*) FROM retail WHERE InvoiceNo LIKE 'C%') AS cancellations,
       (SELECT COUNT(*) FROM retail WHERE InvoiceNo NOT LIKE 'C%' AND (Quantity <= 0 OR UnitPrice <= 0)) AS zero_or_negative,
       (SELECT COUNT(*) FROM retail WHERE InvoiceNo NOT LIKE 'C%' AND Quantity > 0 AND UnitPrice > 0
                                     AND NOT regexp_matches(StockCode, '^[0-9]{5}')) AS non_product_codes,
       COUNT(*) AS clean_lines
FROM clean_lines;

Output

┌───────────┬───────────────┬──────────────────┬───────────────────┬─────────────┐
│ raw_lines │ cancellations │ zero_or_negative │ non_product_codes │ clean_lines │
│   int64   │     int64     │      int64       │       int64       │    int64    │
├───────────┼───────────────┼──────────────────┼───────────────────┼─────────────┤
│    541909 │          9288 │             2517 │              2379 │      527725 │
└───────────┴───────────────┴──────────────────┴───────────────────┴─────────────┘

9,288 + 2,517 + 2,379 + 527,725 = 541,909, so every raw line is accounted for. This is the sentence that goes at the top of your analysis: "Excluded 9,288 cancellation lines, 2,517 zero or negative lines and 2,379 postage, fee and adjustment lines (2.6% of all lines)."

Step 2: clean, shape, present

sql

WITH clean_lines AS (
    SELECT Country, InvoiceNo, CustomerID, Quantity * UnitPrice AS line_revenue
    FROM retail
    WHERE InvoiceNo NOT LIKE 'C%'
      AND Quantity > 0
      AND UnitPrice > 0
      AND regexp_matches(StockCode, '^[0-9]{5}')
),
country_revenue AS (
    SELECT Country,
           COUNT(DISTINCT InvoiceNo)  AS invoices,
           COUNT(DISTINCT CustomerID) AS customers,
           SUM(line_revenue)          AS revenue
    FROM clean_lines
    GROUP BY Country
)
SELECT Country, invoices, customers,
       ROUND(revenue, 2) AS revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM country_revenue
ORDER BY revenue DESC
LIMIT 6;

Output

┌────────────────┬──────────┬───────────┬────────────┬──────────────┐
│    Country     │ invoices │ customers │  revenue   │ pct_of_total │
│    varchar     │  int64   │   int64   │   double   │    double    │
├────────────────┼──────────┼───────────┼────────────┼──────────────┤
│ United Kingdom │    17901 │      3916 │ 8747163.21 │         85.2 │
│ Netherlands    │       93 │         9 │  283889.34 │          2.8 │
│ EIRE           │      282 │         3 │   271164.3 │          2.6 │
│ Germany        │      443 │        94 │  205569.89 │          2.0 │
│ France         │      382 │        87 │  184582.74 │          1.8 │
│ Australia      │       56 │         9 │  138171.31 │          1.3 │
└────────────────┴──────────┴───────────┴────────────┴──────────────┘

Each CTE has one job. clean_lines decides what counts as a sale, country_revenue sets the grain (one row per country), and the final SELECT only presents: percentage, rounding, order. Reading down, a reviewer sees the business story too: the Netherlands brings more revenue than Germany from 9 customers against 94.

Step 3: monthly revenue for the top three export markets, and a bug

Now a longer pipeline: the three biggest non-UK countries in 2011, their monthly revenue, and each month's share of that country's year. A first attempt:

sql

WITH clean_lines AS (
    SELECT Country, InvoiceDate, Quantity * UnitPrice AS line_revenue
    FROM retail
    WHERE InvoiceNo NOT LIKE 'C%'
      AND Quantity > 0
      AND UnitPrice > 0
      AND regexp_matches(StockCode, '^[0-9]{5}')
      AND InvoiceDate >= '2011-01-01' AND InvoiceDate < '2012-01-01'
),
top_countries AS (
    SELECT Country
    FROM clean_lines
    WHERE Country <> 'United Kingdom'
    GROUP BY Country
    ORDER BY SUM(line_revenue) DESC
    LIMIT 3
),
monthly AS (
    SELECT c.Country, date_trunc('month', c.InvoiceDate) AS month,
           SUM(c.line_revenue) AS revenue
    FROM clean_lines AS c
    JOIN top_countries AS t ON t.Country = c.Country
    GROUP BY c.Country, month
)
SELECT Country, strftime(month, '%Y-%m') AS month,
       ROUND(revenue, 2) AS revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY Country), 1) AS pct_of_year
FROM monthly
WHERE month >= '2011-09-01'
ORDER BY Country, month;

Output

┌─────────────┬─────────┬──────────┬─────────────┐
│   Country   │  month  │ revenue  │ pct_of_year │
│   varchar   │ varchar │  double  │   double    │
├─────────────┼─────────┼──────────┼─────────────┤
│ EIRE        │ 2011-09 │ 42486.55 │        41.1 │
│ EIRE        │ 2011-10 │ 23817.92 │        23.1 │
│ EIRE        │ 2011-11 │ 29838.43 │        28.9 │
│ EIRE        │ 2011-12 │  7140.89 │         6.9 │
│ Germany     │ 2011-09 │ 16561.22 │        21.7 │
│ Germany     │ 2011-10 │ 28422.92 │        37.3 │
│ Germany     │ 2011-11 │  24252.7 │        31.8 │
│ Germany     │ 2011-12 │  7024.17 │         9.2 │
│ Netherlands │ 2011-09 │ 26832.26 │        25.7 │
│ Netherlands │ 2011-10 │ 40378.65 │        38.6 │
│ Netherlands │ 2011-11 │ 25640.01 │        24.5 │
│ Netherlands │ 2011-12 │ 11728.02 │        11.2 │
└─────────────┴─────────┴──────────┴─────────────┘
  12 rows                              4 columns

Sanity check: EIRE's four "share of year" values add to 100%. Four months cannot be the whole year. The window function ran after the final WHERE, so SUM(revenue) OVER (PARTITION BY Country) only saw September to December. The pipeline makes the fix easy: compute the share in its own step, before any filter.

sql

-- same clean_lines, top_countries and monthly CTEs as above, then:
with_share AS (
    SELECT Country, month, revenue,
           100.0 * revenue / SUM(revenue) OVER (PARTITION BY Country) AS pct_of_year
    FROM monthly
)
SELECT Country, strftime(month, '%Y-%m') AS month,
       ROUND(revenue, 2) AS revenue,
       ROUND(pct_of_year, 1) AS pct_of_year
FROM with_share
WHERE month >= '2011-09-01'
ORDER BY Country, month;

Output

┌─────────────┬─────────┬──────────┬─────────────┐
│   Country   │  month  │ revenue  │ pct_of_year │
│   varchar   │ varchar │  double  │   double    │
├─────────────┼─────────┼──────────┼─────────────┤
│ EIRE        │ 2011-09 │ 42486.55 │        16.2 │
│ EIRE        │ 2011-10 │ 23817.92 │         9.1 │
│ EIRE        │ 2011-11 │ 29838.43 │        11.4 │
│ EIRE        │ 2011-12 │  7140.89 │         2.7 │
│ Germany     │ 2011-09 │ 16561.22 │         8.6 │
│ Germany     │ 2011-10 │ 28422.92 │        14.8 │
│ Germany     │ 2011-11 │  24252.7 │        12.6 │
│ Germany     │ 2011-12 │  7024.17 │         3.7 │
│ Netherlands │ 2011-09 │ 26832.26 │         9.8 │
│ Netherlands │ 2011-10 │ 40378.65 │        14.7 │
│ Netherlands │ 2011-11 │ 25640.01 │         9.3 │
│ Netherlands │ 2011-12 │ 11728.02 │         4.3 │
└─────────────┴─────────┴──────────┴─────────────┘
  12 rows                              4 columns

Now September is 16.2% of Ireland's year: one month in six of revenue, a real pattern worth asking the sales team about. December is low in every country because the data stops on 9 December; a chart should label it as a partial month.

Step 4: debugging one step on its own

Which three countries did top_countries actually pick? Keep the CTEs and select from the step you want to inspect:

sql

WITH clean_lines AS (
    SELECT Country, InvoiceDate, Quantity * UnitPrice AS line_revenue
    FROM retail
    WHERE InvoiceNo NOT LIKE 'C%'
      AND Quantity > 0
      AND UnitPrice > 0
      AND regexp_matches(StockCode, '^[0-9]{5}')
      AND InvoiceDate >= '2011-01-01' AND InvoiceDate < '2012-01-01'
),
top_countries AS (
    SELECT Country, ROUND(SUM(line_revenue), 2) AS revenue_2011
    FROM clean_lines
    WHERE Country <> 'United Kingdom'
    GROUP BY Country
    ORDER BY SUM(line_revenue) DESC
    LIMIT 3
)
SELECT * FROM top_countries;

Output

┌─────────────┬──────────────┐
│   Country   │ revenue_2011 │
│   varchar   │    double    │
├─────────────┼──────────────┤
│ Netherlands │    275134.86 │
│ EIRE        │    261481.04 │
│ Germany     │    192290.75 │
└─────────────┴──────────────┘

Checking it costs one line of SQL, and it confirms the fixed share: 42,486.55 / 261,481.04 = 16.2% for Ireland in September. Adding the yearly total as a column while debugging is a habit worth keeping.

Step 5: inlined or materialised in PostgreSQL

On the invoice_lines table (538,998 rows, indexed on invoice_no), the same CTE with and without MATERIALIZED:

sql

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON)
WITH lines AS (SELECT * FROM invoice_lines)
SELECT count(*) FROM lines WHERE invoice_no = '536366';

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON)
WITH lines AS MATERIALIZED (SELECT * FROM invoice_lines)
SELECT count(*) FROM lines WHERE invoice_no = '536366';

PostgreSQL

                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using invoice_lines_invoice_no_idx on invoice_lines (actual rows=2 loops=1)
         Index Cond: (invoice_no = '536366'::text)
         Heap Fetches: 2
 Planning Time: 0.809 ms
 Execution Time: 0.243 ms
 
                           QUERY PLAN
----------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   CTE lines
     ->  Seq Scan on invoice_lines (actual rows=538998 loops=1)
   ->  CTE Scan on lines (actual rows=2 loops=1)
         Filter: (invoice_no = '536366'::text)
         Rows Removed by Filter: 538996
 Planning Time: 0.121 ms
 Execution Time: 133.928 ms

Inlined, the filter went straight to the index: 2 rows read, 0.24 ms. Materialised, PostgreSQL first copied all 538,998 rows into the CTE and then threw away 538,996 of them: 134 ms, about 550 times slower. Leave the default alone unless you have measured; use MATERIALIZED for an expensive step that the query reads several times.

Common mistakes

One giant CTE that does everything. It is just a subquery with a name. One idea per step.
Filtering before a window that needs the whole set. As in Step 3, compute shares and ranks in their own CTE, filter after.
Not stating what cleaning removed. Put the counts in the analysis. Reviewers trust numbers they can reconcile.
Assuming a CTE runs once. In SQL Server and many warehouses a CTE used three times can run three times. Use a temp table for heavy reused steps.
Forcing MATERIALIZED out of habit. It blocks the optimiser from pushing filters down, as Step 5 showed.
Names like cte1, t2, final. Names are documentation. top_countries says what it holds.
Forgetting a CTE ends with the statement. The next query cannot see it; make a temp table or view if you need it again.

Interview angle

When would you use a CTE instead of a subquery or a temporary table?

A CTE when the query has several logical steps and I want it readable and debuggable, or when a step is used more than once in the same query. A subquery for a small one-off filter. A temp table when a heavy intermediate result is reused by several statements, needs an index, or I want to inspect it while I work. A view when the logic should be shared with other people or tools. I would also mention that in PostgreSQL 12+ a single-use CTE is inlined, so it costs nothing, while some engines may evaluate a CTE each time it is referenced.

How do you approach a long SQL question in a live interview?

Say the plan out loud first, as CTE names: "clean_lines removes cancellations; monthly aggregates by country and month; with_share adds the percentage; then I filter and order." Confirm the grain of each step with the interviewer, write the CTEs in that order, and run or reason about each one before the next. It shows structured thinking even if time runs out.

Is a CTE faster than a subquery?

Usually neither: in PostgreSQL 12+, SQL Server and most warehouses a single-use CTE is planned like the equivalent subquery. Differences come from materialisation. A materialised CTE can be slower when the outer query needs few rows (no filter pushdown) and faster when an expensive step is reused. Check the plan instead of guessing.

Practice task

For the top five non-UK countries by 2011 revenue, build one query with a CTE per step that returns:

The country's 2011 revenue from clean lines.
Its best month and that month's share of the country's year.
How many 2011 lines the cleaning rules removed for that country.

Done when the query has at least four named CTEs, each doing one thing, and every share is computed against the full year.

Show a solution

sql

WITH lines_2011 AS (
    SELECT *
    FROM retail
    WHERE InvoiceDate >= '2011-01-01' AND InvoiceDate < '2012-01-01'
),
clean_lines AS (
    SELECT Country, InvoiceDate, Quantity * UnitPrice AS line_revenue
    FROM lines_2011
    WHERE InvoiceNo NOT LIKE 'C%'
      AND Quantity > 0
      AND UnitPrice > 0
      AND regexp_matches(StockCode, '^[0-9]{5}')
),
removed AS (
    SELECT Country, COUNT(*) AS raw_lines
    FROM lines_2011
    GROUP BY Country
),
top_countries AS (
    SELECT Country, SUM(line_revenue) AS revenue_2011, COUNT(*) AS clean_lines
    FROM clean_lines
    WHERE Country <> 'United Kingdom'
    GROUP BY Country
    ORDER BY revenue_2011 DESC
    LIMIT 5
),
monthly AS (
    SELECT c.Country, date_trunc('month', c.InvoiceDate) AS month,
           SUM(c.line_revenue) AS revenue
    FROM clean_lines AS c
    JOIN top_countries AS t ON t.Country = c.Country
    GROUP BY c.Country, month
),
best_month AS (
    SELECT Country, month, revenue,
           ROW_NUMBER() OVER (PARTITION BY Country ORDER BY revenue DESC, month) AS rn
    FROM monthly
)
SELECT t.Country,
       ROUND(t.revenue_2011, 2) AS revenue_2011,
       strftime(b.month, '%Y-%m') AS best_month,
       ROUND(100.0 * b.revenue / t.revenue_2011, 1) AS best_month_pct,
       r.raw_lines - t.clean_lines AS lines_removed
FROM top_countries AS t
JOIN best_month AS b ON b.Country = t.Country AND b.rn = 1
JOIN removed AS r ON r.Country = t.Country
ORDER BY t.revenue_2011 DESC;

Output

┌─────────────┬──────────────┬────────────┬────────────────┬───────────────┐
│   Country   │ revenue_2011 │ best_month │ best_month_pct │ lines_removed │
│   varchar   │    double    │  varchar   │     double     │     int64     │
├─────────────┼──────────────┼────────────┼────────────────┼───────────────┤
│ Netherlands │    275134.86 │ 2011-10    │           14.7 │            47 │
│ EIRE        │    261481.04 │ 2011-09    │           16.2 │           380 │
│ Germany     │    192290.75 │ 2011-10    │           14.8 │           758 │
│ France      │    176052.43 │ 2011-11    │           16.6 │           434 │
│ Australia   │    137138.46 │ 2011-06    │           18.4 │            75 │
└─────────────┴──────────────┴────────────┴────────────────┴───────────────┘

Every share divides by revenue_2011 from top_countries, which was computed before any month was picked, so it is a true share of the year. Germany lost 758 of its 2011 lines to cleaning: 401 cancellation lines, 348 postage lines, 8 manual adjustments and 1 zero-price line. Breaking lines_removed down by rule like that is a good extra CTE to add.

#Why it matters

#The concept

#WITH syntax

#One idea per CTE

#Debugging a CTE

#CTE vs subquery vs temp table vs view

#Materialisation: is the CTE run once or merged?

#A team style guide

#Worked example: country revenue for a UK gift shop

#Step 1: the cleaning step, and what it removed

#Step 2: clean, shape, present

#Step 3: monthly revenue for the top three export markets, and a bug

#Step 4: debugging one step on its own

#Step 5: inlined or materialised in PostgreSQL

#Common mistakes

#Interview angle

#Practice task

#Further reading

Why it matters

The concept

WITH syntax

One idea per CTE

Debugging a CTE

CTE vs subquery vs temp table vs view

Materialisation: is the CTE run once or merged?

A team style guide

Worked example: country revenue for a UK gift shop

Step 1: the cleaning step, and what it removed

Step 2: clean, shape, present

Step 3: monthly revenue for the top three export markets, and a bug

Step 4: debugging one step on its own

Step 5: inlined or materialised in PostgreSQL

Common mistakes

Interview angle

Practice task

Further reading