skip to content

How would you refactor a query with four levels of nested subqueries into stacked CTEs?

level: seniorimportance: should knowfreq 50%

answer

  1. start at the deepest parenthesis
  2. one level becomes one named step
  3. names should read like the business
  4. prove the result set is unchanged
  5. a step used twice is written once

basics

~20 s

Work from the innermost subquery outward: lift each level into its own named CTE in the WITH list, in dependency order, each selecting from the previous one. The result set must be unchanged; only the shape and the names are new.

solid answer

~50 s

Refactor **inside-out**: the deepest derived table becomes the first CTE, the next level becomes the second and selects from it, and so on, leaving the outermost query as the final statement. Name each step after what it means in the business — `paid`, `per_region`, `ranked` — not after its mechanics. Two disciplines keep it safe. First, it is a pure restructuring: nothing about filtering, grouping or join semantics may change, so verify by comparing the old and new result sets rather than trusting the edit. Second, collapse duplication as you go — a subquery that appeared twice becomes one named CTE referenced twice, which is where most of the real gain lives. Push back where the nesting is only two levels deep and already readable: converting a single inline subquery into a CTE used once adds ceremony without adding clarity.

code

sql · 11 lines
sql
SELECT region, avg_order
FROM (
    SELECT region, AVG(total) AS avg_order
    FROM (
        SELECT region, total
        FROM orders
        WHERE status = 'PAID'
    ) paid
    GROUP BY region
) per_region
WHERE avg_order > 500;

go deeper

for a junior

Practise turning a two-level nested subquery into a WITH clause by hand so the inside-out order and the comma-separated list become automatic.

for a middle

Explain the step-by-step lift and why it is behaviour-preserving, plus which fragments must be copied verbatim to keep it that way.

for a senior

Demonstrate the discipline: an equivalence check in both directions, awareness of the outer-join predicate trap, and the judgment to leave already-readable queries alone.

for a principal

Frame it as a maintenance policy — when a repeatedly re-derived step should stop being copied into every query and become a modelled, tested artefact instead.

## The problem the refactor solves Deeply nested subqueries have to be read from the innermost parenthesis outward, which is the opposite of the order the work happens. Each level is anonymous: a reviewer sees `per_region`, or worse `t3`, and must reconstruct what it holds. And a step needed twice must be written twice, so the two copies drift apart over time. ## The mechanical procedure 1. **Find the innermost derived table.** Cut it out, give it a name, and make it the first entry in a `WITH` list. 2. **Move outward one level at a time.** Each level becomes the next CTE and references the name you just created instead of the removed parentheses. 3. **Leave the outermost query as the final statement.** It now selects from the last CTE. 4. **Rename as you go.** The names are the point: `paid`, `per_customer`, `region_avg` say what a step is; `t1`, `sub2` do not. 5. **Collapse duplicates.** Any subquery text that appeared more than once becomes one CTE referenced from several places. ```sql -- before SELECT region, avg_order FROM ( SELECT region, AVG(total) AS avg_order FROM ( SELECT region, total FROM orders WHERE status = 'PAID' ) paid GROUP BY region ) per_region WHERE avg_order > 500; -- after WITH paid AS ( SELECT region, total FROM orders WHERE status = 'PAID' ), per_region AS ( SELECT region, AVG(total) AS avg_order FROM paid GROUP BY region ) SELECT region, avg_order FROM per_region WHERE avg_order > 500; ``` ## Proving you changed nothing This is a behaviour-preserving refactor, and the way to keep it that way is to check rather than to trust. Run the old and the new query against the same data, compare row counts, then compare the result sets themselves — an `EXCEPT` in both directions between the two queries should return no rows either way. Do this on realistic data volumes and with realistic `NULL`s, because a mistake made during the lift almost always shows up as a changed row count on edge cases rather than as an error. Two lifts are especially easy to get wrong. Moving a predicate to a different level changes semantics whenever an outer join is involved, and dropping a `DISTINCT` or a row limit that was buried inside an inner level changes the row multiset. Copy each fragment verbatim first; tidy it only after the equivalence check passes. ## What you gain, concretely - **Reading order matches intent.** Top to bottom, each name defined before it is used. - **Reviewable diffs.** A future change touches one named block, so the diff says which step changed. - **Debuggability.** Point the final statement at any intermediate CTE and inspect that step in isolation without editing anything else. - **Single definition per concept.** "What counts as an active customer" appears once. ## When not to do it A two-level query that already reads fine gains nothing. A CTE referenced once and named `step1` is ceremony. And a chain of ten CTEs where each does one trivial thing is a new readability problem in place of the old one — name the steps a domain expert would name. If a step is genuinely needed by many *different* queries, the refactor to reach for is not a longer `WITH` list but a shared object, since a CTE cannot outlive its statement.

  • How do you verify the refactor did not change the result?
    Compare row counts first, then run the two queries against each other with EXCEPT in both directions; both comparisons must come back empty. Do it on realistic data including NULLs and duplicate keys, since lift mistakes usually surface as a changed row count rather than an error.
  • Which lifts most often change semantics by accident?
    Moving a predicate to a different nesting level when an outer join is involved, and dropping a DISTINCT, ORDER BY or row limit that was buried in an inner level. Copy each fragment verbatim into its CTE first and only tidy it after the equivalence check passes.
  • When would you not convert a subquery into a CTE?
    When it is one shallow inline subquery used once and already readable in place — naming it adds a hop for the reader without adding meaning. The refactor pays off with depth, with repetition, or when a step deserves a domain name.

saying these in an interview costs you the question

  • Renames steps t1, t2, t3 and calls it readable
  • Assumes the rewrite is safe without comparing results
  • Moves predicates between levels while restructuring
  • Wraps every single-use subquery in its own CTE
  • Claims the refactor changes what the query returns

context

resolving links…