0
Challenge · 26Composing Queries: CTEs & Window Functions
CTEs: name a step, read top to bottom
SQLused in ~65% of apps
DevLog wants a "most active authors" panel. You reach for a subquery, then need a second number derived from the first, so you nest another subquery inside it. Three levels deep, indented four times, you scroll up to remember what the innermost one even returns. Nobody can review that query, including you, next Tuesday.
Core idea. A CTE — write WITH name AS (query) — lets you name an intermediate result once and read the rest of the statement top to bottom, like a recipe: step one, step two, then the dish. Any step (or the final SELECT) may use any step named ABOVE it; nothing may reach forward to a step named below.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
with … as ( … )read top to bottom, each step names the one before it
The real query. It can see every CTE named above it — post_counts AND ranked — top to bottom, like reading a recipe.
The rule: a CTE may reference any CTE (or table) named above it, and the final query may reference any of them. Nothing may reference a CTE named below it — there is no forward reference, only a chain.
↑ play a little first, then dig in