Growth Notes

Common Table Expressions

Tags: web-dev analytics concept

Common Table Expressions

Date: 2026-09-27

A named, query-scoped intermediate result. WITH turns one nested query read inside-out into a sequence of steps read top to bottom — which is the difference between an analytics query you can check and one you have to trust.

A common table expression (CTE) is a temporary named result set defined with WITH at the top of a query, which the rest of the query can reference like a table. It exists only for that one statement.

Nested vs named

The question: repeat-purchase rate by first-order channel.

Nested — read from the innermost bracket outwards:

SELECT first_channel, AVG(CASE WHEN n_orders > 1 THEN 1.0 ELSE 0 END) AS repeat_rate
FROM (
  SELECT c.customer_id, c.first_channel, COUNT(o.order_id) AS n_orders
  FROM (
    SELECT customer_id, channel AS first_channel
    FROM (
      SELECT customer_id, channel,
             ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS rn
      FROM orders
    ) numbered WHERE rn = 1
  ) c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.first_channel
) per_customer
GROUP BY first_channel;

As CTEs — the same query, read in the order it happens:

WITH numbered AS (            -- 1. number each customer's orders
  SELECT customer_id, channel,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS rn
  FROM orders
),
first_orders AS (             -- 2. one row per customer: where they came from
  SELECT customer_id, channel AS first_channel
  FROM numbered WHERE rn = 1
),
per_customer AS (             -- 3. one row per customer: how many orders
  SELECT customer_id, COUNT(*) AS n_orders
  FROM orders GROUP BY customer_id
)
SELECT f.first_channel,
       AVG(CASE WHEN p.n_orders > 1 THEN 1.0 ELSE 0 END) AS repeat_rate
FROM first_orders f
JOIN per_customer p USING (customer_id)   -- both one row per customer: no fan-out
GROUP BY f.first_channel;

Notice each CTE has a stated grain — one row per order, then one per customer. Joining two things that are both one-row-per-customer can’t fan out, which is exactly the check Joins asks for. The nested version hides the grain inside the brackets.

Why it’s the default for analytics SQL

  • Each step is checkable. Swap the final SELECT for SELECT * FROM first_orders LIMIT 20 and inspect the intermediate. Debugging becomes bisection
  • Names carry intent. first_orders says what a subquery aliased c doesn’t
  • Reuse within the query. One CTE can be referenced several times — two different aggregations off the same filtered base
  • It’s the shape dbt models take — each model is effectively a CTE promoted to a table, and dbt style guides write models as a chain of CTEs internally [CHECK: current dbt style guide wording]

Recursive CTEs

WITH RECURSIVE lets a CTE reference itself — a base case, then a step repeated until it returns no rows. The job it does in web work: walking a hierarchy of unknown depth — category trees, referral chains, org charts.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories WHERE id = 42              -- base: start at "Footwear"
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id          -- step: children of anything already found
)
SELECT * FROM tree;                          -- stops when a step finds no new children
iteration  rows added
base       42 Footwear
1          57 Trainers, 58 Boots
2          91 Running, 92 Walking      (children of Trainers)
3          none                        → stop

A cycle in the data loops forever — Postgres has a CYCLE clause, and elsewhere a depth cap in the step (WHERE t.depth < 20) is the guard. The other common use is generating a series (every date in a range) to join against so days with zero orders appear as zero rather than vanishing — SQL Dialects has the per-engine shortcuts.

Performance: inlined or materialised

A CTE is a naming device, not a promise about execution. The planner may:

  • Inline it — substitute it into the main query as if it were a subquery, so filters from outside can be pushed into it. Usually what you want
  • Materialise it — compute it once into a temporary result and read from that. Better when it’s expensive and referenced several times; worse when an outside filter could have shrunk it
Postgres 12+   inlines a non-recursive CTE referenced once; you can force
               either with MATERIALIZED / NOT MATERIALIZED
earlier        always materialised — CTEs were an "optimisation fence"

Old advice that “CTEs are slow” dates from that fence. [CHECK: BigQuery’s behaviour — it generally does not cache a CTE referenced twice, so each reference can re-scan; verify before relying on it for cost.] When a CTE really needs computing once, a temporary table is the explicit version — Query Performance · Query Planning.

Where not to reach for one

  • A single simple subquery — WHERE id IN (SELECT ...) — doesn’t gain from a name
  • Something reused across many queries — that’s a view or a model, not a CTE copied between files

Graph View

Backlinks