SQL for Analytics
Intermediate 13 min read SQL for Analytics

CTEs and Subqueries

Building an analysis in named steps

Common Table Expressions (WITH clauses) name intermediate steps so complex analytics read top-to-bottom. They are the closest SQL gets to building blocks — readable, composable and testable.

CTEWITHsubqueryderived tablecomposablereadability
Prerequisites: SQL Aggregations
Before we start

What you will learn

  • Write CTEs and nested subqueries
  • Compose multi-stage analytics with CTEs
  • Decide CTE vs subquery vs temporary table
Introduction

What is this?

A CTE (Common Table Expression) names a query result for reuse within one statement: WITH revenue AS (...) SELECT ... FROM revenue. Subqueries inline the same idea. CTEs turn a monster query into a readable pipeline of named stages and let you reference an intermediate result multiple times — the main reason analysts prefer them.

Motivation

Why do you need it?

  • Readability: an analysis reads like a sequence of named steps, not a single wall of SQL.
  • Composability: build a chain (raw → filtered → aggregated → joined).
  • Debugging: you can run each CTE in isolation to check intermediate outputs.
  • Reuse within a query: one CTE referenced twice without copy-pasting.
Think about it

Mental model

Imagine

A recipe broken into named steps (chop, marinate, grill)

Think of

CTEs

You can peek at any step's bowl (run the CTE) and you follow the flow name by name — instead of one paragraph of 200 words that must be read as a single sentence.

Mechanics

How it works

  • WITH name AS (SELECT ...), other AS (SELECT ...) declares named steps before the main query.
  • The main query references CTE names like tables.
  • Nested subqueries put the derived table inline: SELECT * FROM (SELECT ... ) x.
  • Multiple references to one CTE can be reused without duplication.
  • CTEs are statement-scoped; for cross-click reuse you'd promote to a view or temp table.

Composing analysis with CTEs.

CTE 1: raw
CTE 2: metrics
CTE 3: join
Main SELECT
Hands-on

Examples

Three-stage analysis with CTEs

Three-stage analysis with CTEs
WITH purchases AS (
  SELECT user_id, amount, day
  FROM orders
  WHERE status = 'completed'
),
monthly AS (
  SELECT user_id, DATE_TRUNC('month', day) AS ym, SUM(amount) AS spent
  FROM purchases
  GROUP BY 1, 2
),
top_users AS (
  SELECT user_id, MAX(spent) AS best_month
  FROM monthly
  GROUP BY user_id
  HAVING MAX(spent) >= 100
)
SELECT t.user_id, u.country, t.best_month
FROM top_users t
JOIN users u ON u.id = t.user_id
ORDER BY t.best_month DESC;

Each WITH block is independently readable and testable; the final SELECT ties them to a dimension and output.

Real world

In production

  • Funnel queries built as consecutive CTEs (view → cart → checkout → complete).
  • Reconciling reports: a 'calc' CTE referenced twice for a before/after comparison.
  • Named metric definitions reused across a query without duplication.
Guidelines

Best practices

  • Name CTEs with clear intent (raw_orders, clean_metrics) — they ARE documentation.
  • Split long chains into stages; each CTE should be inspectable alone.
  • Prefer WHERE-to-filter early; a CTE that filters nothing is a code smell.
  • Use one SELECT per statement; don't tuck final output into an outer subquery.
Watch out

Common mistakes

  • Writing a 9-CTE monster when 3 would do — each step must earn its name.
  • Repeating the same CTE logic across queries instead of promoting to a view.
  • Filtering at the end of a chain what should have been filtered at the start.
  • CTE name collisions shadowing real tables.
When things break

Debugging tips

  • Run each CTE by itself to locate where the shape changes.
  • Add row counts per stage to see exactly where rows disappear.
  • Compare a CTE's output against the raw table when a number looks off.
  • If output 'builds down', your main query forgot a filter; check the final WHERE.
Interviews

Interview checkpoint

Practice

Hands-on lab

Compose a Funnel With CTEs
30 min · intermediate
In progress
Objectives
  • Write multi-stage CTE chains
  • Compute stage-to-stage conversion
  • Debug one CTE in isolation

Compose a Funnel With CTEs

30 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

What keyword introduces a CTE?

2

A CTE can only be referenced once in a statement.

Go deeper

Official documentation

PostgreSQL WITH queries
docs