Lesson 9 of 12 · 25 min
Multi-step CTE pipelines
By the end of this lesson you can design a CTE pipeline for a multi-step business metric, keep every step at a known grain, test each step on its own, and combine steps with the right join.
A single CTE names one step. The real power appears when steps build on each other: each CTE can read the ones defined above it, so a WITH clause becomes a pipeline. A good pipeline mirrors how you would explain the calculation to a colleague:
- Base rows: which records are in scope (which orders, which period, which statuses).
- Per-entity totals: aggregate each source to the grain you need (per product, per order, per customer), separately.
- Combine: join the per-entity results, which are now one row per key, so nothing multiplies.
- Final metrics: ratios, differences, labels, sorting.
Let's build one. The operations director asks: what share of units sold is returned, per department?
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons