CTE (WITH clause)

Also called: common table expression

A named subquery defined with WITH at the top of a query, so a long calculation can be written and checked in readable steps.

FormulaWITH step AS (…) SELECT … FROM step

How we work it through

1
Write one step per idea: per buyer, then the share.
2
Run each step alone and check its row count.
3
Name steps after what one row is.

On the practice dataset

The query we run on Meridian, Atterna’s practice marketplace, and what it returns.

WITH per_buyer AS (
  SELECT buyer_id, COUNT(DISTINCT checkout_id) AS purchases
  FROM meridian.orders WHERE status = 'paid'
  GROUP BY buyer_id)
SELECT COUNT(*) AS buyers,
       COUNT(*) FILTER (WHERE purchases >= 2) AS repeat
FROM per_buyer;

Result3,652 buyers, 2,020 of them came back for another purchase

The common trap

Treating each step as correct because the final query runs. Check the row count after every step.

When an AI answers

An AI assistant

“Repeat rate is 0%: nobody bought twice.”

What it didn’t know: Its CTE grouped by order_id instead of buyer_id, so every “buyer” had exactly one order.
Ask back: “What is one row of your first step? Show its row count.”

Paste a real AI answer into the checker — it writes these questions for you →