IntermediateSQL · 25 min
CTEs (Common Table Expressions): Intermediate
Past the basics, CTEs are not a syntax question; they are a design question. The interviewer assumes you can write the WITH clause. What they are testing now is whether you know when to force materialization, when a CTE turns into an anti-pattern, how recursive CTEs fit into a real pipeline, and how to use CTEs as scaffolding for data quality gates. The query shape is the same; the decisions about each CTE's role in the pipeline are where the question lives.
Reason about CTE materialization vs inlining and when to force each
Reach for recursive CTEs for hierarchy walking, series generation, and bounded iteration
Use CTE patterns for data quality assertions and pipeline scaffolding
Recognize when a CTE chain has crossed into anti-pattern territory and refactor
Structuring a Multi-CTE Query
Identify when a problem benefits from named intermediate results: multi-step logic, reused subqueries, or readability.
Here is the question that recurs in interviews past the basics. 'You have a transactions table, a customers table, and a refunds table. Compute net monthly revenue per customer segment, with refunds applied, and validate that no segment shows negative net revenue for more than two consecutive months.' The candidate who writes one giant CTE chain that mixes the aggregation, the validation, and the output is producing a query that works but cannot be reviewed. The candidate who builds a CTE pipeline with explicit data quality checkpoints (one CTE that should return zero rows if the data is clean; a final SELECT that fails loudly if it does not) writes a query that is also a test.
You are being tested on CTE design (not just syntax) when you hear:
- ▸"compute X, then validate that Y"
- ▸"walk a hierarchy and aggregate per level"
- ▸"generate a date range and join actuals onto it"
- ▸"this query is slow; how would you change it"
- ▸"this CTE is referenced three times; what would you do"
The three design questions
Three questions sit underneath every design-grade CTE conversation. First: is this CTE materialized, inlined, or does the engine decide? The optimizer's behavior affects whether predicate pushdown works, whether multiple references re-execute, and whether the query plan you see is the plan the engine actually runs. Second: is recursion the right tool here, or am I forcing recursion into a problem that has a non-recursive solution? Third: is this CTE a query-building block, or is it a data quality gate that should fail loudly if its output is non-empty? Each question pushes the candidate from syntax into design.
The three design questions:
- ▸Is this CTE materialized, inlined, or does the engine decide?
- ▸Is recursion the right tool, or is there a non-recursive solution?
- ▸Is this CTE a query-building block, or a data quality gate?
- ▸Each question pushes the candidate from syntax into design
•Mid-level answer that flatlines
- Writes a CTE chain without thinking about materialization
- Reaches for recursion as the first tool for any hierarchy question
- Treats every CTE as a query-building block; never as an assertion
- Adds CTEs as the query grows without refactoring
✓Mid-level answer that lands
- Names the materialization decision out loud (inline vs MATERIALIZED)
- Defends recursive CTE choice with a depth-and-fanout argument
- Uses one CTE per pipeline stage and one CTE per data quality check
- Refactors the chain when it crosses 8-10 CTEs
Why the design layer matters at this level
Mid-level data engineers own production pipelines. The CTEs they write are not throwaway interview answers; they are the queries that run nightly, that get debugged at 2am, that get inherited by the next person on the team. The interviewer is checking whether you treat each CTE as a deliberate choice (with a name, a contract, a materialization strategy, a quality check) rather than as a syntactic block. The choice articulation is the signal.
State the design choice when you write each CTE. 'This CTE is the aggregation step; it produces one row per (customer, month). This next CTE is a data quality check; it returns rows only if any segment shows negative revenue, and the final SELECT should fail loudly if it has rows.' Naming the role of each CTE is the move that proves you treat the query as a design, not a string.
CTE vs Subquery vs Temp Table
Write a clean chain of 2-4 CTEs where each builds on the previous, with meaningful names that tell a story.
On modern engines, the default behavior for a CTE is to inline it into the surrounding query. The optimizer treats the CTE as if it were a nested subquery in the FROM clause and applies the same optimizations: predicate pushdown, join reordering, common subexpression elimination. Most of the time this is what you want. There is one case where it bites, and one syntactic tool to force the engine's hand.
When inlining helps and when it hurts
Inlining helps in the common case: a CTE that filters or shapes data, referenced once in the final SELECT. The optimizer pushes the WHERE clause from the outer query into the CTE's body, evaluates the combined predicate against the source, and reads only the rows it needs. The CTE is a readability wrapper; the cost equals an equivalent subquery. Inlining hurts in one case: a CTE that is expensive to compute and referenced multiple times in the same query. With inlining, the engine re-computes the CTE for each reference. With materialization, it computes once and reuses. For a CTE that costs 30 seconds and is referenced three times, inlining costs 90 seconds; materialization costs 30 plus the read-back.
Three references to expensive_aggregate from the same final SELECT. With MATERIALIZED, the engine computes the aggregate once and joins the materialized result to itself three times. Without MATERIALIZED, the engine inlines and re-computes the aggregate three times. For a billion-row source, that is the difference between a 30-second query and a 90-second query.
- let the engine inline; predicate pushdown from the outer query helps.
- force MATERIALIZED; the body computes once, the references read from the materialized result.
- let inline; the optimizer may deduplicate via common subexpression elimination.
Engine-specific syntax
Postgres 12+ supports the WITH ... AS MATERIALIZED hint to force materialization and WITH ... AS NOT MATERIALIZED to force inlining. Before Postgres 12, all CTEs were materialized (which is why advice from older Postgres tutorials sometimes recommends rewriting CTEs as subqueries for performance; that advice is outdated on modern engines). Snowflake, BigQuery, and SQL Server do not have a direct equivalent of the MATERIALIZED hint; they choose between inlining and materialization based on the optimizer's cost estimate. On those engines, the workaround is to materialize manually into a temp table or write the result to a session-scoped table before the main query.
•Force MATERIALIZED when
- The CTE body is expensive (large aggregate, complex window function)
- The CTE is referenced multiple times in the same query
- You can prove via EXPLAIN that the engine is re-computing on each reference
- On Postgres 12+ where the hint is supported
✓Let the engine inline when
- The CTE is referenced exactly once (most common case)
- Predicate pushdown from the outer query would speed up the inner query
- The CTE body is cheap (a simple filter or projection)
- On engines without explicit hints; trust the cost-based optimizer
How to tell which the engine chose
EXPLAIN the query and look at the plan. A materialized CTE shows up as a single 'CTE Scan' node with the CTE's name; references to it are reads from that node. An inlined CTE shows up substituted into the outer query's plan as if it were never separately defined; the CTE name does not appear in the plan tree. State this when the interviewer asks about performance: 'I would EXPLAIN the query to see whether the engine inlined or materialized the CTE, then decide whether to add a hint.' Reading the plan, not guessing, is the move.
Other modern optimizations CTEs do not break
On modern engines, CTEs do not block predicate pushdown, partition pruning, projection pruning, or join reordering when they are inlined. The optimizer treats them as part of the outer query. This is a sharp departure from legacy Postgres, where CTEs were an optimization fence that prevented these optimizations. If your mental model of CTE performance is calibrated to legacy Postgres advice, update it: on the engines you actually work with, the CTE is usually as fast as a subquery and sometimes faster (because the optimizer can deduplicate common subexpressions more easily).
TIP
When the interviewer asks 'is this query slow because of the CTEs?', the answer is almost always no. Slow queries are slow because of bad join order, missing indexes, unaligned partitioning, or scanning more data than needed. CTEs are a readability tool that lets you see those problems. State this clearly: 'CTEs themselves are not the performance issue. The cost lives in the joins, the aggregates, and the data movement. I would EXPLAIN the query and look at the expensive nodes, not at the CTE structure.'
Materialization and the Optimizer Fence
Explain the tradeoffs: CTEs are syntactic (most engines inline them), temp tables materialize, subqueries nest.
Recursive CTEs are the only standard SQL syntax for iteration. The engine runs an anchor query, then iterates a recursive step until no new rows are produced. The shape is the same across engines: WITH RECURSIVE name AS (anchor UNION ALL recursive_step). Past the basics, the question is rarely about syntax. It is about when to reach for recursion at all, and when a window function or self-join is cleaner.
The canonical recursive shape
Three pieces. The anchor produces the starting row. The recursive step produces the next row by referencing the CTE itself. The WHERE clause inside the recursive step is the termination condition; without it, the recursion runs until the engine's recursion limit. The UNION ALL combines the anchor and all iterations into the final result. This is the date-spine pattern; almost every period-over-period or rolling-window analysis uses it.
Three common variants
Three patterns recur in data engineering work. First: date spines (generate every day, week, or month between two dates). Second: hierarchy walking (all employees under a manager, all comments in a thread). Third: bounded iteration (compute a running total without window functions, walk a sparse graph). The date-spine is the most common in reporting work and is often the right alternative to a generate_series call that is not portable across engines.
When recursion is the wrong tool
Two cases where candidates reach for recursion when they should not. First: running totals. SUM OVER with a partition and frame is the right tool, not a recursive CTE. Recursion was the historical workaround on engines without window functions; modern engines have window functions and the SUM OVER form is faster and clearer. Second: fixed-depth hierarchy walks. If the question says 'employees, their manager, and their manager's manager,' that is a two-level chain of LEFT JOINs; recursion is overkill. Reach for recursion when depth is unbounded; reach for joins or windows when it is bounded.
Cases where recursion is the wrong tool:
- ▸Running totals: SUM OVER with a frame, not a recursive CTE
- ▸Fixed-depth hierarchies: chained LEFT JOINs, not recursion
- ▸Adjacent-row comparisons: LAG and LEAD over a partition
- ▸Pair finding within a table: self-join with an inequality
•Use a recursive CTE for
- Date spines and series generation (every day in a range, every integer 1 to N)
- Unbounded hierarchy traversal (all reports, all descendants, full subtree)
- Iteration where each step depends on the prior one in non-window ways
- Generating cumulative paths or arrays that window functions cannot produce
✓Reach for window functions or joins instead when
- Running totals or rolling sums (SUM OVER with a frame is faster and cleaner)
- Fixed-depth hierarchy walks (two or three LEFT JOINs)
- Sequential value comparisons (LAG and LEAD over a partition)
- Pair finding within a table (self-join with an inequality)
Cycle detection and depth bounds belong in every production CTE
Cycle detection and depth bounds are not just advanced topics; they belong in every production recursive CTE. Real hierarchies have cycles from temporary reorgs and bad imports; real depth varies enough that a depth bound is the safety net against runaway iteration. The interviewer is checking whether you write both into the query unprompted. State this when writing: 'I'm adding the path array for cycle detection and an explicit depth bound; both belong in any production recursive CTE, not just the ones I expect to have cycles.' That sentence is the production-experience signal.
Series generation as a portable date spine
Postgres has generate_series; BigQuery has GENERATE_DATE_ARRAY; Snowflake has GENERATOR. Each engine has a different syntax for generating a sequence. The recursive CTE date-spine pattern is portable across all of them; it works on any engine that supports recursive CTEs, which is most modern engines. When the interviewer hands you a question that needs a date spine and asks you to make it portable, the recursive CTE is the answer that ships across engines without modification.
The recursive CTE has one syntactic gotcha across engines: some require the WITH RECURSIVE keyword (Postgres, MySQL 8); some make recursion implicit when the CTE references itself (SQL Server). State the engine you are writing for: 'on Postgres I would write WITH RECURSIVE; on SQL Server WITH alone is sufficient.' Engine-aware writing is the production-experience signal.
Reusing a CTE Across Multiple Branches
Write a recursive CTE for org charts, date spines, and graph traversal, and explain the base + recursive structure.
A technique most candidates have not seen: CTEs as data quality gates. A pipeline query that computes a result is also a query that should verify the result is correct. Write a CTE that should return zero rows when the data is clean. Have the final SELECT either return the violations (for inspection) or raise an error (for pipeline enforcement). This pattern is the conceptual foundation of dbt's tests, Great Expectations checks, and most data-quality frameworks; you can implement it in plain SQL with CTEs.
The assertion CTE pattern
Five CTEs. monthly_gross and monthly_refunds compute the aggregates. net_revenue joins them. quality_violations is the gate: any (segment, month) with negative net revenue is a violation. The final SELECT returns the violations. If the data is clean, the result set is empty. If there are violations, the result set is non-empty and the consumer can inspect each one. This is the same pattern dbt uses for its 'singular tests': write a SELECT that returns rows representing violations, then assert the result is empty.
monthly_gross: aggregate transactionsmonthly_refunds: aggregate refundsnet_revenue: join the aggregatesquality_violations: rows that should not existFinal SELECT: return the violations (or empty if clean)
Two consumption modes for the violations CTE
Mode one: human inspection. The final SELECT returns the violations; an engineer reads them and decides whether to fix the data or the query. Mode two: pipeline gate. The query is wrapped in a check (Airflow sensor, dbt test, custom assertion) that fails the pipeline if the result is non-empty. State the mode when the interviewer asks: 'I would write the violations CTE either way; the pipeline framework wraps it as a hard gate that fails on non-empty, or an analyst reads the result for investigation.' One CTE serves both modes.
Composability: stacking quality checks
The pattern extends. Multiple quality checks become multiple CTEs, each producing rows that represent violations of a specific contract, UNION ALL'd together for the final inspection.
- net revenue should never be negative; rows that are negative are violations.
- values above a sanity threshold may signal a unit error or a data import bug.
- every (segment, month) in the date spine should have a row; missing rows are gaps.
CTE-driven idempotent writes
Another design pattern: use a CTE chain to compute the rows that should exist, then write them with INSERT ... SELECT or MERGE in a way that is idempotent (re-running produces the same result). The CTE chain is the spec for the desired state; the INSERT or MERGE is the write. This is how dbt's incremental models work: the SELECT is a CTE chain that returns the rows that should be in the target; dbt handles the write semantics. State the pattern when the interviewer asks about pipeline ergonomics: 'I'd compute the desired state in CTEs and use INSERT ... ON CONFLICT or MERGE for the idempotent write; re-running produces the same target state.'
•CTE chain as a one-shot query
- Final SELECT returns the answer
- Pipeline framework runs the query and consumes the result
- No validation; downstream consumer trusts the output
- Bugs surface in production when the consumer notices
✓CTE chain as a pipeline artifact
- Final SELECT returns either the answer or the violations
- Pipeline framework checks the violations CTE before consuming
- Validation is part of the query, not a separate test
- Bugs surface in CI when the violations CTE returns rows
The interviewer often probes this depth indirectly: 'how would you know if this query produced wrong results?' The answer is the violations CTE pattern. The query that produces the data also produces the assertions about the data; if the assertions ever return rows, the consumer knows something is wrong without needing a separate test.
Refactoring Nested Subqueries into CTEs
Practice narrating CTE logic step-by-step, which interviewers use to gauge your communication and clarity of thought.
CTEs are a power tool. The same tool that makes a 50-line query readable can produce a 500-line query that is unreadable for a different reason: too many tiny CTEs, each doing one tiny thing, with the actual logic distributed across the chain. The interviewer is checking whether you recognize the anti-patterns and refactor before they ship to production.
The 20-CTE monster
If a query has 20 CTEs, something has gone wrong. Either the query is solving a problem that should be decomposed across multiple queries or views, or the CTEs are too small (each doing one trivial transformation that could be folded into the next), or the author was nesting CTEs as a substitute for thinking about the data flow. The rule of thumb: 3 to 7 CTEs per query is normal; 8 to 12 is the upper end; past 15 the query is signaling that you should split it. Tell the interviewer this when you write past 8 CTEs: 'this is getting long; I would consider whether some of these belong in a view or a staging table.'
The shadow-OOP anti-pattern
Some candidates structure their CTEs as if they were object-oriented classes: customer_base, customer_enriched, customer_with_metrics, customer_final. Each CTE adds one column. The chain is technically correct but produces six tables that exist only to add data piecemeal. The fix is to fold related additions into one CTE that produces the full enriched table in one step. The CTE chain should reflect the query's logic, not an OOP class hierarchy.
•Shadow-OOP anti-pattern
- customer_base: SELECT * FROM customers
- customer_with_revenue: add revenue column
- customer_with_orders: add order count
- customer_with_segment: add segment column
- customer_final: SELECT * FROM customer_with_segment
✓Pipeline that actually decomposes
- customer_summary: customer + revenue + order count + segment in one CTE
- monthly_metrics: aggregated metrics in one CTE
- ranked: window function applied to monthly_metrics
- Final SELECT: filter and format
CTE-driven debugging at scale
When a CTE chain produces wrong results, the debugging strategy is: SELECT from each CTE in turn, in order, until you find the first one that produces unexpected output. The CTE before the broken one is the input; the CTE after is downstream of the bug. This bisection is what makes CTEs a debugging tool, but only if the CTEs are named meaningfully and produce intermediate results that can be inspected. If your CTEs are doing micro-tasks and you cannot tell from the name what should be in each, you have lost the debugging benefit.
Real-world failure mode
At Lyft in 2021, a senior data engineer inherited a marketplace-attribution query that had grown to 47 CTEs across 600 lines. The query had been correct two years earlier; nobody had touched it since because no one could read it. A new bug had crept in (revenue was being double-counted for partner-referred rides) but the original author was no longer at the company. The fix took three days, mostly spent reading the chain top-down and figuring out which CTE owned which transformation. The post-mortem rule the team wrote was 'any analytical SQL past 10 CTEs in this codebase gets refactored before merge; the threshold is a code-review block, not a guideline.' The next quarter saw a sharp drop in production bugs in the marketplace metrics; the refactor cost was paid forward in maintainability.
Refactoring strategies
Three strategies for shrinking a CTE chain. First: fold sibling enrichments. If three CTEs each add one column and have the same input, combine them into one CTE that adds all three. Second: promote stable intermediates to views. If three different queries use the same monthly_gross_revenue CTE, that CTE belongs in a view. Third: split into staged queries. If the chain crosses 15 CTEs, the work might be two queries: one writes to a staging table, the other reads from it. Each strategy reduces complexity at a different level.
Refactor when a chain crosses 10 CTEs:
- ▸Fold sibling enrichments into one CTE if they share the same input
- ▸Promote stable intermediates to views if other queries also need them
- ▸Split into staged queries: one writes a staging table, the other reads
| Situation | Phrasing that flatlines | Phrasing that lands |
|---|---|---|
| Query has 15+ CTEs | "It works." | "This is past the readability threshold for one query. I'd split it into a staging step and a reporting step, or promote the stable intermediates to views." |
| A CTE is referenced three times | "Whatever the engine does." | "On Postgres 12+ I'd add WITH ... AS MATERIALIZED so the body computes once. On other engines I'd write to a temp table." |
| The interviewer asks about recursion | "I'd use a recursive CTE." | "Depends on the depth. Bounded depth gets chained joins; unbounded gets a recursive CTE with cycle detection and a depth bound; high-read at scale gets a materialized closure table." |
| The interviewer asks 'how would you know this is right' | "I'd write tests." | "I'd add a violations CTE inside this query: rows that violate a contract become the result. If the result is empty the data is clean; if not, the pipeline framework fails on it." |
| Each CTE adds one column | "More CTEs is more readable." | "Folding sibling enrichments into one CTE is cleaner. Each CTE should reflect a logical step, not one column." |
The closing summary
Close with a four-sentence wrap. 'CTEs are a design tool at this level, not a syntax question. Each CTE has a role: aggregation step, enrichment, ranking, data quality gate. Materialization is the engine's default; force MATERIALIZED only when a CTE is expensive and referenced multiple times. Past 10 CTEs the query is signaling that it should be split into staged queries or promoted to views; the refactor before merge is cheaper than the refactor at 2am.' Four sentences. Design, materialization, quality gates, refactor threshold. The shape generalizes to every CTE-heavy production query.
❯❯❯PUTTING IT ALL TOGETHER
> You are in a data engineering interview at an e-commerce company. The interviewer asks: 'Compute net monthly revenue per customer segment with refunds applied, then validate that no segment has negative net revenue. The query needs to run nightly and the team treats data quality violations as a hard failure.'
You frame the pipeline as four CTEs plus a violations gate. monthly_gross aggregates transactions, monthly_refunds aggregates refunds, net_revenue joins them, and quality_violations returns any (segment, month) with net_revenue < 0.
You say: 'The final SELECT returns the violations. If clean, the result is empty; if dirty, the pipeline framework fails on it. The same query computes the data and validates the data.'
Follow-up: 'The monthly_gross CTE is referenced from net_revenue and from a downstream period-over-period query. How would you optimize?' You say: 'On Postgres 12+ I'd add WITH monthly_gross AS MATERIALIZED so the body computes once. On Snowflake or BigQuery I'd materialize manually into a temp table, since those engines do not support the hint.'
Follow-up: 'How would you handle missing months for a segment that had no transactions?' You say: 'Recursive CTE date spine; CROSS JOIN with segments; LEFT JOIN actuals onto the spine; COALESCE missing revenue to zero. The violations CTE catches segments that had a month with zero revenue and a refund.'
Follow-up: 'When would you NOT use a CTE here?' You say: 'If this grows past 10 CTEs I'd split into staged queries: one writes to a staging table, the other reads. Or promote stable intermediates like monthly_gross to a view if other queries also need it.'
Closing: 'CTEs at this level are a design tool. Each one has a role (aggregation, enrichment, validation). Materialization is the engine's default; force MATERIALIZED only when the CTE is expensive and referenced multiple times. The violations CTE is the production-experience signal.'
KEY TAKEAWAYS
On modern engines a
CTE is inlined by default, so predicate pushdown, partition pruning, projection pruning, and join reordering all still work. The legacy Postgres advice that CTEs are an optimization fence stopped being true in Postgres 12.Inlining only hurts when an expensive
CTE is referenced multiple times, because the engine recomputes it per reference. Postgres 12 and later take WITH ... AS MATERIALIZED; Snowflake, BigQuery, and SQL Server have no equivalent hint, so materialize into a temp table instead.Read the plan rather than guessing: a materialized CTE appears as a single
CTE Scan node under its own name, while an inlined one is substituted into the outer plan and its name never appears.Reach for
WITH RECURSIVE when depth is unbounded (date spines, hierarchy walks) and not when it is bounded. Running totals belong in SUM() OVER, and a two-level manager chain belongs in two LEFT JOINs. Every production recursive CTE carries a path array for cycle detection and an explicit depth bound.Give each CTE a role and say it out loud: aggregation step, enrichment, ranking, or data quality gate. The violations CTE that returns rows only on a contract breach is the same shape dbt uses for singular tests, and it serves both human inspection and a hard pipeline gate.
Three to seven CTEs per query is normal and eight to twelve is the upper end. Past ten, fold sibling enrichments that share an input, promote reused intermediates to views, or split into staged queries. The 47-CTE chain nobody can read is the failure mode.
CTEs do not make queries faster; they make your interview answer readable
- Category
- SQL
- Difficulty
- intermediate
- Duration
- 25 minutes
- Challenges
- 0 hands-on challenges
Topics covered: Structuring a Multi-CTE Query, CTE vs Subquery vs Temp Table, Materialization and the Optimizer Fence, Reusing a CTE Across Multiple Branches, Refactoring Nested Subqueries into CTEs
Lesson Sections
- Structuring a Multi-CTE Query (concepts: sqlCte)
The three design questions Three questions sit underneath every design-grade CTE conversation. First: is this CTE materialized, inlined, or does the engine decide? The optimizer's behavior affects whether predicate pushdown works, whether multiple references re-execute, and whether the query plan you see is the plan the engine actually runs. Second: is recursion the right tool here, or am I forcing recursion into a problem that has a non-recursive solution? Third: is this CTE a query-building bl
- CTE vs Subquery vs Temp Table (concepts: sqlCte)
On modern engines, the default behavior for a CTE is to inline it into the surrounding query. The optimizer treats the CTE as if it were a nested subquery in the FROM clause and applies the same optimizations: predicate pushdown, join reordering, common subexpression elimination. Most of the time this is what you want. There is one case where it bites, and one syntactic tool to force the engine's hand. When inlining helps and when it hurts Inlining helps in the common case: a CTE that filters or
- Materialization and the Optimizer Fence (concepts: sqlRecursiveCte)
Recursive CTEs are the only standard SQL syntax for iteration. The engine runs an anchor query, then iterates a recursive step until no new rows are produced. The shape is the same across engines: WITH RECURSIVE name AS (anchor UNION ALL recursive_step). Past the basics, the question is rarely about syntax. It is about when to reach for recursion at all, and when a window function or self-join is cleaner. The canonical recursive shape Three pieces. The anchor produces the starting row. The recur
- Reusing a CTE Across Multiple Branches (concepts: sqlCte)
The assertion CTE pattern Five CTEs. monthly_gross and monthly_refunds compute the aggregates. net_revenue joins them. quality_violations is the gate: any (segment, month) with negative net revenue is a violation. The final SELECT returns the violations. If the data is clean, the result set is empty. If there are violations, the result set is non-empty and the consumer can inspect each one. This is the same pattern dbt uses for its 'singular tests': write a SELECT that returns rows representing
- Refactoring Nested Subqueries into CTEs (concepts: sqlCte)
CTEs are a power tool. The same tool that makes a 50-line query readable can produce a 500-line query that is unreadable for a different reason: too many tiny CTEs, each doing one tiny thing, with the actual logic distributed across the chain. The interviewer is checking whether you recognize the anti-patterns and refactor before they ship to production. The 20-CTE monster If a query has 20 CTEs, something has gone wrong. Either the query is solving a problem that should be decomposed across mul