SQL
Intermediate 14 min read SQL

CTEs and Query Design

Writing SQL other analysts can audit

Common Table Expressions (WITH clauses) turn monster SQL into readable, testable stages. Good query design is the maintainability habit that keeps report queries trustworthy.

CTEWITHquery designreadabilitymodular SQLtesting
Prerequisites: Joining for Analysis
Before we start

What you will learn

  • Structure analytical queries as named, single-purpose CTEs
  • Debug a complex query stage by stage
  • Decide when a CTE should become a real model/view
Introduction

What is this?

CTEs (WITH ... AS) name a subquery so the rest of the query reads like a pipeline: raw → cleaned → aggregated → formatted. They do not change performance semantics much in most engines (they may be inlined), but they transform an un-auditable 200-line query into reviewable stages.

Motivation

Why do you need it?

  • Long queries are bug-magnets; CTE stages localize where a value went wrong.
  • Named stages make the intent visible ('clean_orders', 'region_totals') to reviewers and future-you.
  • CTEs enable unit-testing habits: run one stage and assert its output.
  • A well-designed query is the first step toward promoting it into a modeled view.
Think about it

Mental model

Imagine

A chef's mise en place

Think of

CTE query stages

Ingredients prepped, labeled and lined up before cooking. Each CTE is a prep station — you can verify each bowl before it goes into the pan, and nobody has to read one giant recipe wall of text.

Mechanics

How it works

  • WITH defines reusable named subqueries before the main SELECT.
  • Each CTE consumes earlier CTEs, forming a readable pipeline.
  • Recursive CTEs support calendars, hierarchies and path-finding.
  • Engines typically inline CTEs (materialization is a performance choice to verify).
  • CLLUTION: run any CTE in isolation to test it before the main query.

A staged pipeline inside one query.

Stage 1
Stage 2
Stage 3
Final SELECT
Hands-on

Examples

A readable three-stage query

A readable three-stage query
WITH valid_orders AS (
  SELECT * FROM orders WHERE status = 'confirmed'
),
monthly AS (
  SELECT date_trunc('month', ts) AS month, SUM(amount) AS revenue
  FROM valid_orders GROUP BY 1
)
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev
FROM monthly ORDER BY month;

Stage one cleans, stage two aggregates, stage three reads for the report. Each stage is separately reviewable and testable.

Real world

In production

  • A retention query builds events → cohorts → matrix as three CTEs, and each is verified in CI.
  • A finance reconciliation query uses CTEs for journal, ledger and diff — each tested against fixtures.
  • A recursive CTE generates a calendar to backfill empty report periods.
Guidelines

Best practices

  • One idea per CTE; name it by what it produces, not by the tool.
  • Keep stages filter-early so later stages work on smaller data.
  • Test boundary stages independently (run just the CTE).
  • Promote stable, reused CTE logic into real views/models with tests and lineage.
Watch out

Common mistakes

  • One giant SELECT with subqueries nested to oblivion.
  • Recomputing the same aggregation in every branch (write it once as a CTE).
  • Naming CTEs vaguely ('x', 'temp') so review is impossible.
  • Assuming CTEs are always materialized — check the plan before relying on it.
Root cause analysis

Common issues

Error
Slow query despite clean CTEs
Root cause

Inlining recomputed a stage repeatedly in some engines

Fix

Check EXPLAIN; materialize the hot CTE or build it as a real table.

Error
Best-practice CTE refactor changed results
Root cause

Semantics changed (a filter moved stages)

Fix

Diff results before/after refactor the same way you'd diff test suites.

When things break

Debugging tips

  • Run each CTE separately and inspect output before assembling.
  • Put row-count assertions at stage boundaries for early failure.
  • Isolate a wrong value by bisecting CTEs in half the time.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

The main benefit of structuring a query with CTEs is:

2

CTEs always prevent engines from re-computing an expensive stage.

Go deeper

Official documentation

PostgreSQL WITH (CTE) docs
docs