Master SQL Common Table Expressions (WITH clauses) on 62 challenges whose reference solution uses WITH — 1 Easy, 22 Medium, 39 Hard. 26 are free to play, including 3 free Hard previews; 19 chain two or more CTEs and 3 are recursive. Write cleaner, more readable SQL.
Start Practicing Free →Every challenge below is solved with a chain — WITH a AS (…), b AS (…) — where a later CTE reads from an earlier one and the final SELECT pulls from the last step. Start with Multi-CTE Revenue Pipeline, the one written for exactly this pattern. The five Medium ones are free, and so are the two Hard previews.
Counted from the challenge bank, September 2026.
Two CTEs of DISTINCT values — countries, categories — CROSS JOINed, then a LEFT JOIN to orders to count. The empty cells are the answer.
Try it →Same shape with a twist: distinct memberships × distinct countries, then a LEFT JOIN through customers to orders.
Try it →One CTE filters the quarter; a second, built on it, finds the 95th-percentile cutoff with OFFSET; the query keeps banks above it. Banking track, FDIC data.
Try it →Three CTEs: filter the quarter, sort it, pick the median row with OFFSET — then join the banks above the median. Banking track.
Try it →Spend per account in one CTE, disputes per account in another, then LEFT JOIN the two summaries. Join both children at row level instead and every transaction repeats once per chargeback.
Try it →Uses: SELECT CTE LEFT JOIN GROUP BY julianday COUNT
Spend per customer → DENSE_RANK over that CTE → join customers for name and membership. Written for exactly this pattern; start here.
Try it →First-order date per customer → new customers per day from that → a running SUM, because COUNT(DISTINCT) can't be a window.
Try it →LAG in one CTE, a new-session flag in the next, a running SUM in the final SELECT. Meta's sessionization pattern.
Try it →LAG → running-sum streak id → GROUP BY with HAVING COUNT >= 3. Gaps and islands.
Try it →DISTINCT dates → a ROW_NUMBER grouping key → islands, then CASE labels each one flash / burst / streak / marathon.
Try it →Cohort month per customer, activity joined back to cohorts, cohort sizes — three CTEs the final SELECT joins for a retention rate.
Try it →A top-10 CTE feeds a LAG CTE; the query turns it into quarter-over-quarter growth. Banking track.
Try it →Filter → mean → standard deviation (SQLite has no STDDEV, you build it) → z-scores above 2. Banking track.
Try it →A mean CTE, a stddev CTE built on it, then every transaction past 3σ with its z-score. Fraud dataset, Banking track.
Try it →One CTE tags each transaction with its merchant's risk tier and whether it was disputed; a second aggregates by tier and category; a window then sets each cell beside its tier's own rate.
Try it →Spend per account, then ROW_NUMBER and COUNT(*) OVER () in the CTE built on it — so the decile cut-off is computed from the book, never typed.
Try it →Uses: SELECT CTE Window Functions NTILE JOIN SUM
Uses: SELECT CTE Window Functions ROW_NUMBER JOIN GROUP BY
Chaining is what people usually mean by "nested CTEs". For the walkthrough — and the difference from a WITH literally nested inside another CTE — see multiple CTEs in the CTE tutorial. A CTE that references itself is a different mechanism: recursive CTEs explained.
A CTE that references itself — WITH RECURSIVE t AS (anchor UNION ALL step FROM t) — walks a hierarchy one level per iteration: an org chart from the CEO down, a team's size rolled up through every report, a chain of chargebacks traced back to the first transaction. All three are Hard, which is Pro. For the mechanism — anchor member, recursive member, termination — read recursive CTEs explained.
Counted from the challenge bank, September 2026.
Finance & Banking track · Uses: Recursive CTE JOIN UNION ALL