Common Table Expressions and Subqueries Questions

Covers writing and structuring complex SQL queries using Common Table Expressions and subqueries, including when to prefer one approach over another for readability, maintainability, and performance. Candidates should be able to author WITH clauses to break multi step logic into clear stages, implement recursive CTEs for hierarchical data, and use subqueries in SELECT, FROM, and WHERE clauses. This topic also includes understanding correlated versus non correlated subqueries, how subqueries interact with joins and window functions, and practical guidance on choosing CTEs, subqueries, or joins based on clarity and execution characteristics. Interviewers may probe syntax, typical pitfalls, refactoring nested queries into CTEs, testing and validating each step of a CTE pipeline, and trade offs that affect execution plans and index usage.

HardTechnical

28 practiced

Explain how writing predicates incorrectly inside CTEs can prevent partition pruning or index usage. Given a partitioned table by day, show an example predicate that disables pruning and rewrite it to enable pruning and use indexes effectively.

Sample Answer

Partition pruning and index usage rely on the planner knowing the partition key predicate at plan time so it can avoid scanning partitions and use index seeks. Two common mistakes prevent that: 1) putting the predicate inside a materialized CTE (an optimization fence in older Postgres / default materialization semantics), and 2) expressing the predicate with non-constant/unstable expressions so the planner can’t evaluate it at plan time.

Bad (disables pruning / index use):

-- CTE is materialized; predicate inside prevents pushdown/pruning
WITH recent AS (
  SELECT * FROM events   -- events is partitioned by event_day
  WHERE event_day >= current_date - interval '7 days'
)
SELECT count(*) FROM recent WHERE user_id = 123;

Because the CTE may be materialized, the planner can’t push the event_day filter down to the partitioned table; it may scan all partitions and then filter, missing partition pruning and index on (event_day,user_id).

Better options to enable pruning and index use:

1) Push the predicate to the outer query (or avoid materializing):

-- Predicate applied directly to partitioned table; planner can prune
SELECT count(*)
FROM events
WHERE event_day >= current_date - interval '7 days'
  AND user_id = 123;

2) If you need a CTE for readability but want pushdown, mark it NOT MATERIALIZED (Postgres >=12):

WITH recent AS NOT MATERIALIZED (
  SELECT * FROM events
  WHERE event_day >= current_date - interval '7 days'
)
SELECT count(*) FROM recent WHERE user_id = 123;

3) Ensure the predicate is a planner-evaluable expression (avoid wrapper functions or runtime-only values). Prefer direct column comparisons to constants or stable functions:

-- Use a concrete cutoff variable computed once so planner can consider it
SELECT count(*) FROM events
WHERE event_day >= (CURRENT_DATE - 7)  -- simple date arithmetic
  AND user_id = 123;

Why this matters:- Materialized CTE blocks predicate pushdown; planner may materialize and scan every partition.- Non-evaluable predicates (volatile functions, concatenation, prepared-statement parameter nuances) can prevent pruning.- When planner sees a direct constraint on the partition key it prunes partitions and can use per-partition indexes, drastically reducing IO.

If you must use dynamic parameters in prepared statements, consider explicit query construction, prepared-plan-aware techniques (EXECUTE with constants), or ensure expressions are simple and immutable so the planner can prune.

MediumTechnical

35 practiced

When would you use a recursive CTE for hierarchical queries versus using a graph database or denormalized closure table? Discuss considerations such as expected tree depth, update frequency, ad-hoc queries, and analytics performance.

Sample Answer

Short answer: use recursive CTEs for simple, shallow hierarchies with infrequent heavy traversal and many ad-hoc SQL queries; use a closure table when you need fast, repeated ancestor/descendant lookups and analytic joins; use a graph DB when relationships are deep, highly connected, or queries traverse many hops with graph-specific algorithms (shortest path, centrality) or when write/read patterns favour native graph operations.

Considerations:

- Expected tree depth and connectivity: - Recursive CTE: fine for shallow trees (tens of levels). Depth beyond hundreds risks performance and stack/iteration limits. - Closure table: O(1) lookup of any ancestor/descendant, best for deep trees queried often. - Graph DB: best for highly connected graphs or variable-length traversals across many hops.

- Update frequency: - Recursive CTE: no extra bookkeeping; best if updates are frequent but traversals are occasional. - Closure table: expensive updates—must maintain many rows on inserts/moves—good if reads far outnumber writes. - Graph DB: supports frequent relationship updates efficiently; transactions can manage complex updates.

- Ad-hoc queries & analyst workflow: - Recursive CTE: easily used in ad-hoc SQL, joins with existing columnar tables, good for quick exploration and feature engineering. - Closure table: also SQL-friendly and performant for analytics; simpler joins for feature aggregation. - Graph DB: less convenient for standard SQL tooling; requires export or connectors for ML pipelines.

- Analytics performance: - For batch analytics and feature engineering inside a relational warehouse, closure table or precomputed denormalized tables give predictable, fast aggregation. - Recursive CTEs can be okay for one-off exploration but may be slow at scale. - Graph DB excels at graph algorithms; for large-scale analytics you’ll often extract subgraphs into a graph processing framework or precompute features into the data warehouse.

Recommendation for a Data Scientist:- Start with recursive CTEs for exploration and prototyping.- If you move to production features/metrics with frequent reads and deep hierarchies, maintain a closure/denormalized table or materialized views.- Choose a graph DB when you require graph algorithms or live traversals that are inefficient in SQL; still pipeline results back into the data warehouse for model training.

HardTechnical

26 practiced

Subqueries that compute a date threshold like WHERE created_at >= (SELECT MAX(created_at) - INTERVAL '7 days' FROM table) can interfere with index range scans. Explain why and rewrite the pattern to compute the threshold separately so the predicate can use an index or partition pruning.

Sample Answer

Problem: A scalar subquery in the WHERE likeWHERE created_at >= (SELECT MAX(created_at) - INTERVAL '7 days' FROM events)can prevent the planner from treating the right-hand side as a constant during index range scan or partition pruning. If the planner considers the expression per-row, or can't prove it's stable at plan time, it won't generate a simple index range scan (or a partition pruning decision) because it doesn't have a fixed boundary to seek to.

Why this matters:- Index range scans and partition pruning require a known constant (or easily derivable constant at plan time) to compute an index start/stop or to eliminate partitions.- A subquery may be evaluated in a way that the planner can't fold into a constant, forcing a sequential scan or blocking efficient use of indexes.

Rewrite patterns (compute threshold once so predicate is a constant):

1) Compute in the application and pass as a parameter (recommended for large jobs)- Query 1: SELECT MAX(created_at) - INTERVAL '7 days' AS threshold FROM events;- Query 2: SELECT * FROM events WHERE created_at >= :threshold; -- planner can use index/partition pruning

2) Compute once in SQL and cross-join as a scalar constantBad:SELECT * FROM eventsWHERE created_at >= (SELECT MAX(created_at) - INTERVAL '7 days' FROM events);

Better (computes threshold once, predicate is constant per plan):WITH params AS ( SELECT MAX(created_at) - INTERVAL '7 days' AS threshold FROM events)SELECT e.*FROM events eCROSS JOIN params pWHERE e.created_at >= p.threshold;

Or equivalently:SELECT e.*FROM events e, (SELECT MAX(created_at) - INTERVAL '7 days' AS threshold FROM events) pWHERE e.created_at >= p.threshold;

Notes and caveats:- In modern PostgreSQL the planner can inline simple CTEs, but using a scalar FROM-subquery or computing the value client-side guarantees a single evaluation.- For partitioned tables, ensure the threshold is available at plan time (passing as a literal or preparing the plan after computing the threshold yields best partition pruning).- Always verify with EXPLAIN/EXPLAIN ANALYZE that an index range scan or partition pruning is used after the change.

MediumTechnical

28 practiced

Refactor this correlated-subquery style snippet into an equivalent JOIN + GROUP BY to improve performance: SELECT u.user_id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id AND o.status = 'completed') AS completed_orders FROM users u; Provide the rewritten SQL and explain index choices that help the JOIN version.

Sample Answer

Approach: replace the correlated subquery (which executes per row) with a single JOIN + GROUP BY that aggregates orders once. Use a LEFT JOIN so users with zero completed orders are included and use an index (or partial index) to make the aggregation fast.

Rewritten SQL:

sql

SELECT
  u.user_id,
  COALESCE(COUNT(o.order_id), 0) AS completed_orders
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.user_id
  AND o.status = 'completed'
GROUP BY u.user_id;

Why this is faster:- The original correlated subquery runs the WHERE for every user. The JOIN + GROUP BY scans/uses indexes once and aggregates in a single pass.- LEFT JOIN + GROUP BY preserves users with zero completed orders; COALESCE turns NULL to 0.

Index recommendations (PostgreSQL-specific and general):1. Best (partial index for this case): CREATE INDEX idx_orders_user_completed ON orders(user_id) WHERE status = 'completed'; - Very efficient: index entries only for completed orders; ideal for the ON condition with status filter.

2. General composite index: CREATE INDEX idx_orders_user_status ON orders(user_id, status); - Helps when queries filter by user_id and status; can be used for index-only scans if order_id and visibility are available.

Additional notes:- Make sure to ANALYZE the tables so the planner has up-to-date stats.- If you expect frequent writes that change status, consider trade-offs: partial index is smaller but must be maintained on updates.- For very large result sets, ensure ORDER BY / pagination needs are handled separately.

MediumTechnical

32 practiced

From a data-scientist perspective, outline a rule-of-thumb guide for choosing between CTE, subquery, or join in analytics SQL. Cover readability, reusability, potential for materialization, and how to validate performance impact in an iterative model-building workflow.

Sample Answer

Start with the requirements: readability (for you & teammates), reusability, whether the DB can/materializes intermediate results, and performance in iterative model-building (many runs). Rule-of-thumb:

1) Readability / intent- Use CTEs to break complex logic into named steps (feature_a AS (...), feature_b AS (...)) so each transformation is explicit.- Use subqueries for small, one-off filters or aggregations embedded in a single SELECT.- Use joins when combining well-defined dimension tables or when you need to reuse the same derived table multiple times in one query.

2) Reusability- If a step is reused across multiple queries or notebooks, factor into a permanent table/view (or a parameterized macro). CTEs are ephemeral — good for single-query clarity; views/materialized views are better for cross-session reuse.

3) Potential for materialization- If a step is costly and reused across iterations (feature engineering during model experiments), materialize it (temporary/permanent table or a materialized view) to avoid repeated computation.- Some engines (BigQuery, Snowflake) may auto-spill or cache CTEs differently; test assumptions.

4) Performance validation (iterative workflow)- Benchmark: run EXPLAIN/EXPLAIN ANALYZE to inspect plans for CTE inlining vs materialization.- Measure wall time and bytes scanned for representative runs (cold + warm).- Start with readable CTEs; when experiments stabilize, profile and convert hot CTEs to temp tables/materialized views.- Automate: add a small script to run timed queries and collect metrics per experiment.

Examples:

sql

-- readable, single-run:
WITH feat AS (
  SELECT id, SUM(amount) last_30_days FROM events WHERE ts > NOW()-30 GROUP BY id
)
SELECT m.*, f.last_30_days FROM model_input m LEFT JOIN feat f USING(id);

Validation checklist:- EXPLAIN plan shows expected join order and no redundant scans- Compare runtime and cost before/after materializing- Ensure results identical via row counts and key checksums

Summary: prefer CTEs for clarity during exploration, joins for domain merges, subqueries for tiny local logic, and materialize expensive, reused steps once model pipelines stabilize — validate with EXPLAIN and timed benchmarks.

Unlock Full Question Bank

Get access to hundreds of Common Table Expressions and Subqueries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.