CTEs & Subqueries Questions

Common Table Expressions (CTEs) and subqueries in SQL, including syntax, recursive CTEs, usage patterns, performance implications, and techniques for writing clear, efficient queries. Covers when to use CTEs versus subqueries, refactoring patterns, and potential pitfalls.

MediumTechnical

34 practiced

Discuss how CTEs can impact query performance. Include examples of situations where CTEs may cause repeated work or materialization, and describe simple methods (EXPLAIN, ANALYZE, temporary tables, materialized views) to measure and mitigate performance issues when preparing features for ML pipelines.

Sample Answer

CTEs (WITH ... AS ...) are great for readability and modularizing complex SQL, but they can affect performance because some engines re-evaluate or materialize the CTE depending on optimizer behavior. Key impacts:

- Repeated work: If a non-materialized CTE is referenced multiple times (or joined multiple times), some databases will re-run the CTE query each time, causing duplicated computation.Example:

sql

WITH user_events AS (
  SELECT user_id, event_time, feature_value FROM events WHERE event_time > '2024-01-01'
)
SELECT u.user_id, SUM(f.feature_value) FROM user_events u JOIN user_events v ON u.user_id = v.user_id;

If user_events is re-executed per reference, cost multiplies.

- Materialization: Some DBs (older Postgres versions) always materialize CTEs (good when expensive and reused) while modern planners (Postgres ≥12) often inline them unless you force MATERIALIZED. Materialization can reduce repeated computation but increases I/O and temp storage.

How to measure:- EXPLAIN shows the planned operators and whether the CTE is planned as a Subquery Scan, Hash, or CTE Scan.- EXPLAIN ANALYZE runs the query and reports actual runtime and row counts—useful to detect re-execution or unexpected large intermediate results.Example:

sql

EXPLAIN ANALYZE
WITH user_events AS ( ... )
SELECT ...

Mitigation strategies when building ML features:1. Force materialization when beneficial: - In Postgres: WITH user_events AS MATERIALIZED ( ... ) to compute once.2. Use temporary tables for heavy intermediate results:

sql

CREATE TEMP TABLE tmp_user_events AS
SELECT ...;
CREATE INDEX ON tmp_user_events(user_id);

Temporary tables let you add indexes and statistics, and are persisted for the session—good for repeated feature joins.3. Use materialized views for reusable precomputed features across jobs; refresh on a schedule.4. Add appropriate indexes and analyze stats on intermediates to help the planner choose efficient joins.5. Profile and iterate: compare EXPLAIN ANALYZE before/after changes; measure wall time, planning vs execution, I/O, and memory.

Trade-offs:- Temp tables/materialized views use storage and require refresh/cleanup; forcing materialization may increase I/O but reduce CPU repetition.- Prefer inlining for simple CTEs; force materialization or temp storage for expensive, reused computations.

For ML pipelines, a pragmatic approach: EXPLAIN ANALYZE to identify repeated work, then try TEMP TABLES or MATERIALIZED (or explicit materialized views) with indexes; monitor runtime and resource usage and choose the approach that minimizes end-to-end pipeline latency and cost.

MediumTechnical

34 practiced

Write a SQL example that demonstrates a performance regression caused by accidentally referencing a CTE multiple times in different branches of a UNION, and then provide a corrected version that computes the CTE once and reuses the result. Explain why the corrected version is better.

Sample Answer

Bad (CTE re-evaluated in each UNION branch — causes repeated work):

sql

WITH filtered_events AS (
  SELECT user_id, event_time, event_type, value
  FROM events
  WHERE event_time >= '2025-01-01' AND event_type IN ('click','purchase')
)
SELECT user_id, 'summary_a' AS tag, COUNT(*) AS cnt
FROM filtered_events
WHERE event_type = 'click'
GROUP BY user_id

UNION ALL

SELECT user_id, 'summary_b' AS tag, SUM(value) AS total_value
FROM filtered_events
WHERE event_type = 'purchase'
GROUP BY user_id;

Problem: Some engines (or planner settings) will inline/recompute filtered_events separately for each UNION branch, scanning and filtering the large events table twice — expensive for large datasets.

Corrected (compute once, reuse result):

Option A — force single materialization in Postgres:

sql

WITH filtered_events AS MATERIALIZED (
  SELECT user_id, event_time, event_type, value
  FROM events
  WHERE event_time >= '2025-01-01' AND event_type IN ('click','purchase')
)
...
-- same two SELECTs using filtered_events

Option B — explicitly aggregate from a single derived table:

sql

WITH filtered_events AS (
  SELECT user_id, event_type, value
  FROM events
  WHERE event_time >= '2025-01-01' AND event_type IN ('click','purchase')
)
SELECT user_id, 'summary_a' AS tag, COUNT(*) AS cnt
FROM filtered_events
WHERE event_type = 'click'
GROUP BY user_id
UNION ALL
SELECT user_id, 'summary_b' AS tag, SUM(value) AS total_value
FROM filtered_events
WHERE event_type = 'purchase'
GROUP BY user_id;

Why the corrected version is better:- Ensures the filter/scan runs once, reducing I/O and CPU.- Avoids duplicate reads and repeated computation for large tables.- Results in simpler execution plans, lower memory and runtime.- For deterministic reuse, use MATERIALIZED or a temporary table if your engine re-evaluates non-materialized CTEs.

MediumTechnical

37 practiced

Some DBs (e.g., older Redshift versions) materialize CTEs and create temporary results for each CTE. Describe how you would test whether your database materializes CTEs, and write a minimal SQL experiment (two-part query) to demonstrate whether a CTE is re-evaluated each time it is referenced.

Sample Answer

Approach: Use a nondeterministic expression (RANDOM()) inside a CTE and reference the CTE twice. If the DB materializes the CTE once, both references will return identical RANDOM() values; if it re-evaluates, the values will differ.

SQL experiment (minimal, two-part references to the same CTE):

sql

WITH sample_cte AS (
  -- three rows with a nondeterministic value
  SELECT id, RANDOM() AS r
  FROM (VALUES (1),(2),(3)) AS t(id)
)
-- compare the r values from two references to the same CTE
SELECT COUNT(*) AS differing_rows
FROM (
  SELECT id, r AS r1 FROM sample_cte
) a
JOIN (
  SELECT id, r AS r2 FROM sample_cte
) b USING (id)
WHERE a.r1 <> b.r2;

Interpretation:- Result = 0: CTE was materialized once (both references returned identical r).- Result = 3: CTE was re-evaluated for each reference (RANDOM() produced different values per reference).Notes:- Use RANDOM() (or CURRENT_TIMESTAMP, SEQUENCE.NEXTVAL where supported) so repeated evaluation produces different outputs.- Run multiple times to be confident; some optimizers might inline simple queries.

MediumTechnical

39 practiced

Some analytic queries use subqueries in GROUP BY or HAVING clauses. Show an example where a subquery in HAVING can be replaced with a CTE-driven aggregation to improve clarity and possibly performance. Use `orders(order_id, user_id, amount)` and compute users whose total spend exceeds the average user spend.

Sample Answer

Approach: Rather than nesting a subquery inside HAVING (which can be harder to read and may re-compute aggregates), compute per-user totals and the global average in CTEs, then join/compare them. This is clearer and lets the planner optimize each aggregation separately.

Example using CTEs:

sql

WITH user_totals AS (
  SELECT user_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY user_id
),
avg_total AS (
  SELECT AVG(total_spend) AS avg_spend
  FROM user_totals
)
SELECT ut.user_id, ut.total_spend
FROM user_totals ut
CROSS JOIN avg_total a
WHERE ut.total_spend > a.avg_spend
ORDER BY ut.total_spend DESC;

Why this is better:- Clarity: Separates per-user aggregation from global aggregation.- Performance: The DB can materialize or optimize each CTE; avoids recomputing SUM(...) per user for each comparison.- Extensibility: Easy to add filters (e.g., date ranges) in user_totals before computing avg.

Edge cases & notes:- If no users, avg_spend is NULL; comparing to NULL yields no rows — coalesce if desired.- For very large tables, ensure appropriate indexes and consider partial aggregations or approximate functions if exact average is expensive.

HardTechnical

39 practiced

How do partitioning strategies (table partitioning) interact with CTEs and subqueries? Describe how partition pruning can be preserved across CTE boundaries (if at all) and what you should be careful about when writing CTEs that filter on partition keys.

Sample Answer

Short answer: partition pruning (aka partition elimination) depends on the optimizer’s ability to see a partition key predicate as a simple, constant/bind-parameter expression on the base table. Anything that hides or “materializes” the predicate (optimization fence) can prevent pruning. CTEs and some subqueries can either preserve pruning (when inlined/pushed down) or block it (when treated as materialized/isolated).

Key points and examples:- Optimizer must push the filter down to the partitioned table. If the CTE/subquery is inlined (treated like a view), pruning is preserved. If the engine materializes the CTE (creates a temp/result), pruning is lost.- PostgreSQL: CTEs were optimization fences pre-12 (prevented pruning). Since v12 CTEs are inlined by default; you can still force MATERIALIZED which blocks pruning. Example: WITH cte AS (SELECT * FROM events WHERE dt = '2025-01-01') SELECT * FROM cte; — if inlined, partition pruning occurs; if MATERIALIZED, it doesn’t.- SQL Server and MySQL generally inline simple derived tables and push predicates; complex expressions or TOP/ORDER BY can prevent pushdown.- Cloud warehouses differ: BigQuery and Snowflake usually do predicate pushdown but watch for functions or non-deterministic expressions.

What breaks pruning:- Materialized CTEs or explicit temp tables- Wrapping partition key in functions, casts, or expressions the optimizer can’t simplify (e.g., DATE(col) = '2025-01-01' vs col = '2025-01-01')- Non-deterministic predicates or runtime-only values that prevent static partition selection- Using OR across partition key and non-partitioned predicates which complicates elimination- Subqueries that return lists when the optimizer can’t constant-fold them

Best practices for data scientists:- Apply simple, sargable predicates on the partition key at the query top-level whenever possible (e.g., WHERE event_date = '2025-01-01' or WHERE event_date BETWEEN ...).- Avoid wrapping partition keys in functions; precompute derived fields during ETL if needed.- Prefer inlined CTEs or push predicates into the CTE’s definition; if using PostgreSQL, avoid MATERIALIZED unless intentional.- When you must materialize (for correctness or readability), be aware of the performance cost and consider indexing, filtering earlier in ETL, or using temporary partitioned tables.- Test with EXPLAIN/EXPLAIN ANALYZE to confirm partitions scanned. If pruning isn’t happening, rewrite queries (move predicates, avoid function-wrapping) or use engine-specific hints/options.

Takeaway: write predicates so the optimizer can reason about them as constants/bind parameters against the base table; be mindful of CTE materialization behavior in your DB and always validate with explain plans.

Unlock Full Question Bank

Get access to hundreds of CTEs & Subqueries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.