SQL Interview Prep · Lesson

Refactoring Nested Queries Into CTEs

A live-interview pattern: turning an unreadable nested query into stepwise CTEs.

Refactoring Nested Queries Into CTEs is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Live-Interview Refactor

A staple mid-level prompt: here is a query, make it readable. The interviewer hands you a deeply nested SELECT and watches how you decompose it. Turning nesting into a sequence of named CTEs is the cleanest answer.

This lesson walks the exact moves so you can do it calmly on a whiteboard.

Start With the Innermost Query

Nested subqueries execute conceptually from the inside out. So read the query inside-out too: find the deepest parenthesized SELECT first; that is your first pipeline stage.

Give it a descriptive name and lift it into a CTE. Everything that referenced that inner block now references the CTE name instead.

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

Lift One Level Into a CTE

Take that innermost derived table and promote it to a CTE. The outer query stays the same except it now selects from the named CTE.

This single move already removes one layer of mental nesting and gives the step a meaningful name.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

A Genuinely Nested Example

Here is a harder one to refactor: two levels of nesting plus a correlated-style filter. The goal is the average order value among customers in the top spending tier.

It is correct but hard to read. We will peel it apart stage by stage.

SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id
    FROM (
        SELECT customer_id, SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) s
    WHERE s.total > 1000
);

Name the First Stage

The deepest block computes total spend per customer. Lift it into a CTE called spend. Now the middle layer simply filters that CTE.

Notice how each extraction reduces nesting depth by one and adds a self-documenting name.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id FROM spend WHERE total > 1000
);

Name the Second Stage

Extract the filter on spend into its own CTE, big_spenders. The remaining main query becomes a flat join or membership test against a clearly named set.

Each stage now has one responsibility, the hallmark of clean SQL.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id FROM spend WHERE total > 1000
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
JOIN big_spenders b ON b.customer_id = o.customer_id;

Preserve Semantics While Refactoring

The golden rule: a refactor must not change results. Watch for traps that silently alter output:

Switching IN to a JOIN can introduce duplicate rows if the right side is not distinct.
NOT IN with NULLs behaves differently from NOT EXISTS.
Aggregation grain must stay the same.

State these risks aloud to show care.

Verify the Refactor

How do you prove the refactor is faithful? Mention that you would run both versions and compare row counts and a checksum, or diff the result sets on a sample.

In an interview, even narrating I'd validate by comparing counts and a few spot rows demonstrates engineering discipline beyond just rewriting syntax.

SELECT COUNT(*), SUM(amount)
FROM orders
WHERE customer_id IN (SELECT customer_id FROM big_spenders);

When NOT to Refactor

Refactoring is not always an improvement. A single shallow subquery may be clearer left alone, and over-splitting into many tiny CTEs can hurt readability too.

Use judgment: refactor when nesting obscures intent or logic is reused. Tell the interviewer you would stop once the query reads top-to-bottom as discrete, named steps.

The Refactor Checklist

A repeatable method to recite:

Read inside-out to find the deepest subquery.
Lift it into a named CTE.
Repeat upward, one layer at a time.
Name each stage by what it produces.
Confirm results are unchanged (watch IN/JOIN and NULL traps).

This turns a scary nested query into a calm, stepwise rewrite.

Communicating Your Refactor

Talk while you work: The innermost block is per-customer spend, so I'll call it spend. The next layer filters big spenders. Then the outer query averages their order amounts.

Interviewers grade communication as much as correctness. A narrated, stage-by-stage refactor shows exactly the mid-level maturity they want.

Quick Check

Identify the correct first move when refactoring a deeply nested query into CTEs.

Recap: Refactoring Into CTEs

You learned a calm, repeatable refactor: read inside-out, lift the deepest subquery into a named CTE, and work outward one layer at a time.

Name each stage by what it produces.
Preserve semantics; watch IN-vs-JOIN duplicates and NULL traps.
Validate by comparing counts and sample rows.
Do not over-split; stop when the query reads as clear named steps.

That completes the CTE course; you can now refactor confidently in a live interview.

Frequently asked questions

Is the “Refactoring Nested Queries Into CTEs” lesson free?

Yes — the full text of “Refactoring Nested Queries Into CTEs” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Refactoring Nested Queries Into CTEs”?

A live-interview pattern: turning an unreadable nested query into stepwise CTEs. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Refactoring Nested Queries Into CTEs” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep