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

27 practiced

Compare and contrast how Common Table Expressions behave in PostgreSQL, SQL Server, BigQuery, and Snowflake. Focus on inlining vs materialization, support for recursive CTEs, optimization fences, and any vendor-specific syntax or hints that affect performance or correctness.

Sample Answer

Situation (context): As a data engineer designing ETL and analytical queries across multiple warehouses, understanding CTE semantics is crucial for correctness and performance. Below is a focused comparison by vendor covering inlining vs materialization, recursion, optimization fences, and vendor-specific options.

PostgreSQL:- Inlining by default: non-recursive CTEs are treated as query expressions and usually inlined into the outer query planner, allowing optimizer to push predicates and join reordering.- MATERIALIZED / NOT MATERIALIZED hints (since PG 12): you can force materialization or allow inlining. MATERIALIZED can be useful to avoid expensive re-evaluation; NOT MATERIALIZED enables optimization.- Recursive CTEs: supported (WITH RECURSIVE), standard semantics; planner can optimize but recursion itself is iterative and can be slow for deep recursions.- No explicit “optimization fence” except when MATERIALIZED is used.

SQL Server:- CTEs are syntactic sugar for derived tables and are inlined; no general materialization control.- Recursive CTEs supported (WITH ... AS recursive). Performance can be poor for large recursion depths; often replaced with loops or temp tables.- CTEs do not act as optimization fences; optimizer can reorganize inlined logic. Use temp tables or OPTION (MAXDOP) hints for performance control.- Vendor-specific: query hints (OPTION) affect the whole statement.

BigQuery:- CTEs are inlined and treated as query expressions; optimizer aggressively flattens them for performance.- BigQuery historically materialized some CTEs (especially when referenced multiple times) but now often reuses results via internal optimizations; explicit MATERIALIZE hint exists: WITH temp AS MATERIALIZE(...) to force materialization; NOT MATERIALIZE hint to encourage inlining (syntax: WITH temp AS (SELECT ... ) OPTIONS (materialized=true/false) — check current docs).- Recursive CTEs: historically unsupported; BigQuery introduced support for recursive CTEs (WITH RECURSIVE) — but with quotas and performance caveats.- Optimization fences: explicit MATERIALIZE acts as a fence.

Snowflake:- CTEs are generally inlined; the optimizer rewrites and prunes aggressively.- No explicit MATERIALIZED keyword; however Snowflake may auto-materialize intermediate results for cost reasons, especially when reused.- Recursive CTEs: supported (WITH RECURSIVE) but expect limits and possibly slower performance; often use GROWTH functions or Snowflake-specific ways for hierarchical queries when possible.- Vendor-specific: RESULT_SCAN can reference previous query results; using transient/staging tables or clustering keys often better for performance than relying on CTE materialization.

Practical guidance for a data engineer:- Assume inlining by default (Postgres, SQL Server, Snowflake, BigQuery) and test plans.- Force materialization where repeated expensive subqueries exist or when you need a stable intermediate (Postgres MATERIALIZED or BigQuery MATERIALIZE/OPTIONS).- For large recursive workloads, prefer specialized approaches (iterative ETL, staging tables, or engine-specific features).- Always inspect explain/EXPLAIN ANALYZE (or Query Plan UI in cloud warehouses) to confirm whether the CTE was inlined, materialized, or reused; benchmark alternatives (CTE vs temp table vs subquery).

MediumTechnical

34 practiced

You have a multi-CTE ETL query: raw_events -> cleaned -> enriched -> deduplicated -> final_agg. Describe how you would test and validate each CTE step locally and in CI to ensure correctness and prevent regressions, including sampling strategies, checksums, row-count validation, and schema checks.

Sample Answer

Start by treating each CTE as a testable transformation unit with clear input→output contract (schema, cardinality expectations, key uniqueness). For each CTE (raw_events → cleaned → enriched → deduplicated → final_agg) apply the same pattern locally and in CI.

Local dev (iterative, fast feedback)- Lightweight sandbox: run the full query but materialize each CTE to temp tables or files so you can inspect.- Sampling strategies: - Deterministic sample: ORDER BY primary_key LIMIT N or use hash-based sampling to get reproducible subsets:

sql

-- deterministic sample by hashing id
    WHERE abs(mod(hash(id), 100)) < 5  -- ~5% sample

- Edge-case sample: select rows with nulls, extremes, duplicates.- Sanity checks per CTE: - Schema check: verify columns and types (SELECT column_name, data_type FROM information_schema). - Row-count delta: compare input vs output counts and assert expected filters/expansions:

sql

SELECT COUNT(*) FROM cleaned;
    SELECT COUNT(*) FROM raw_events WHERE <clean_filter>;

- Key invariants: non-null primary key, uniqueness:

sql

SELECT id, COUNT(*) FROM deduplicated GROUP BY id HAVING COUNT(*) > 1;

- Checksums / content hash for sampling: compute MD5/sha256 of concatenated ordered fields to detect unintended changes:

sql

SELECT md5(string_agg(col1||'|'||col2, '' ORDER BY id)) FROM enriched_sample;

- Diffing: join input and output sample on PK to assert expected transformations (values changed as intended).

CI (automated, deterministic, fast)- Unit tests per CTE (run in CI framework e.g., dbt tests, pytest + test db, or Spark unit tests): - Use small “golden” fixtures (input rows) stored in repo; run transformation and assert output equals expected rowset. - Property tests: nullability, value ranges, type casts, date bounds, and that deduplication reduces duplicates.- Regression detection: - Checksums of canonical sample or golden output; fail CI if checksum changes. - Row-count assertions with tolerances or exact expectations depending on deterministic logic. - Schema contract tests: assert column names, types, and nullability haven’t changed.- Data quality checks (automated): - Column-level assertions (no negative prices, timestamp in range). - Unique key assertion for final_agg grouping key. - Distributional checks: compare column histograms between baseline and current using statistical distance (KS test) and alert on big shifts.- Integration in CI: - Spin up ephemeral test warehouse (or use local SQLite/duckdb) seeded with fixtures. - Run each CTE in isolation and then full pipeline. Fail fast on first failing CTE with clear logs and sample diffs.- Monitoring + Post-deploy: - Daily checksum and row-count monitoring against production baselines; alert on sudden deviations. - Backfill tests: run pipeline on historical partition(s) and compare aggregates to previously known values.

Examples of automated assertions (pseudo-test):

python

def test_cleaned_schema():
    result = run_cte('cleaned', fixture_raw)
    assert result.columns == ['id','ts','event_type','user_id']
    assert all(result['id'].notnull())

def test_deduplication_reduces_rows():
    raw = run_cte('enriched', fixture_raw)
    dedup = run_cte('deduplicated', fixture_raw)
    assert len(dedup) <= len(raw)
    assert dedup['id'].is_unique

Trade-offs and tips- Use deterministic sampling to make CI reproducible.- Keep golden fixtures small but representative; include edge cases.- Prefer schema + key + checksum + property tests over brittle full-data equality.- For large-scale pipelines, run heavier distributional/regression tests on a schedule (nightly) rather than every PR.

This strategy gives fast local feedback for development and reliable automated guards in CI to prevent regressions while enabling deeper production monitoring.

EasyTechnical

34 practiced

Describe the difference between correlated and non-correlated subqueries. Provide concise SQL examples showing (1) a non-correlated subquery used in WHERE, and (2) a correlated subquery evaluated per outer row. Explain when each type is evaluated during query execution and the performance implications.

Sample Answer

Correlated vs non‑correlated subqueries:

- Non‑correlated: independent query that can be run once; returns a value/set used by the outer query.- Correlated: references columns from the outer query; evaluated once per outer row (or optimized partially by the engine).

Example (1) Non‑correlated in WHERE — evaluated once:

sql

SELECT user_id, email
FROM users
WHERE country IN (
  SELECT country
  FROM high_volume_countries
);

The subquery returns a static set of countries; DB runs it once (or caches result) then filters users.

Example (2) Correlated — evaluated per outer row:

sql

SELECT u.user_id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
    AND o.created_at > NOW() - INTERVAL '30 days'
);

The subquery references u.user_id, so it’s logically evaluated for each user (though query planners may optimize via joins or semi‑joins).

Evaluation & performance:- Non‑correlated: cheaper when result is small or cached; DB executes it once. Good for static lookups.- Correlated: can be expensive at scale because of per‑row checks; better to rewrite as joins/aggregations or use EXISTS/IN with indexed columns, or transform to a JOIN/CTE for set-based processing (e.g., JOIN to a precomputed recent_orders table). As a data engineer, prefer set operations and ensure proper indexing or use engine-specific optimizations (analytics engines may convert correlated subqueries to efficient joins).

MediumTechnical

29 practiced

Write this query: for each user, select their most recent order amount using a correlated scalar subquery: SELECT u.id, (SELECT amount FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 1) AS last_amount FROM users u. Then rewrite it using a CTE and window functions to improve performance for large datasets.

Sample Answer

Using a correlated scalar subquery (simple but can be slow on large tables):

sql

SELECT
  u.id,
  (
    SELECT o.amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC
    LIMIT 1
  ) AS last_amount
FROM users u;

Why this can be slow:- For each user the DB may scan/seek orders; cost grows with users * average cost per lookup.- Even with an index on (user_id, created_at DESC), many random lookups can be expensive at scale.

Better: compute the most recent order per user once with a CTE + window function, then join:

sql

WITH latest_orders AS (
  SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn
  FROM orders
)
SELECT
  u.id,
  lo.amount AS last_amount
FROM users u
LEFT JOIN latest_orders lo
  ON lo.user_id = u.id AND lo.rn = 1;

Key points:- ROW_NUMBER() picks the single newest row per user; tie-breaker (id DESC) ensures deterministic result.- Work done in one scan/sort of orders; much more efficient for large datasets than per-row subqueries.- Ensure an index on orders(user_id, created_at DESC) to speed up partitioning/sorting or use physical clustering.- Use LEFT JOIN so users without orders return NULL.- For very large tables consider using PARTITION BY + MAX(...) with FIRST_VALUE or a grouped aggregate with DISTINCT ON (Postgres) or lateral joins depending on DB for further optimizations.

HardSystem Design

34 practiced

Design an approach to compute reachability (transitive closure) using recursive CTEs for a directed graph stored in edges(src, dst) with hundreds of millions of edges. Discuss feasibility, memory and time trade-offs, alternative strategies (graph DBs, iterative batch processing, Spark GraphX), and how to produce incremental updates when edges change.

Sample Answer

Requirements & constraints:- Input: edges(src, dst) with hundreds of millions of rows.- Output: reachable pairs (transitive closure) or reachability queries (per-source, per-target).- Non-functional: reasonable latency (batch vs interactive), memory limits, ability to update incrementally when edges change.

Feasibility of recursive CTEs:- Standard SQL recursive CTEs are simple for correctness but not suitable at this scale: they perform iterative joins that can blow up intermediate results, consume huge memory, and often time out. Some RDBMS optimize tail recursion, but hundreds of millions of edges will likely exceed single-node capacity.

Recommended architectures (high-level):1) Batch analytic pipeline (preferred for full closure): - Ingest edges into a distributed compute engine (Spark). - Use GraphX or GraphFrames to compute connected components/reachability per source via Pregel-style iterative propagation. - Persist results as a partitioned table keyed by source with destination list (or adjacency bloom filters) in Parquet on S3 / HDFS. - Schedule as daily/weekly job depending on change rate.

2) Incremental + queryable: - Maintain the original edges in a graph store (e.g., JanusGraph, Neo4j, TigerGraph) or a table exposing fast neighborhood queries. - For low-latency reachability, maintain materialized reachability for hot sources; fall back to on-demand bounded BFS in the graph DB.

Time & memory trade-offs:- Full closure is O(V+E) per iteration, but number of iterations equals graph diameter; worst-case intermediate frontier sizes can be huge. Spark/GraphX uses partitioning (hash or vertex-cut) to distribute work; memory pressure reduced by disk spill but increases runtime.- Storing full closure is storage-intensive (can be up to O(V^2) in dense graphs). Use compressed representations: reachability bitsets, sorted lists, or transitive reduction when suitable.

Alternative strategies:- Spark GraphX/GraphFrames: scalable, integrates with data lake; good for batch closure and supports Pregel API for iterative propagation.- Bulk synchronous parallel graph systems (Giraph) or GraphBLAS implementations for performance.- Graph databases (Neo4j, TigerGraph): better for ad-hoc traversals and incremental updates; may struggle for full closure computation at hundreds of millions of edges unless distributed enterprise edition used.- SQL-based iterative approach: implement iterative join in Spark SQL or use delta tables to avoid recursive CTEs in OLTP DB.

Incremental updates:- Use delta processing: - Capture edge changes via CDC into a changelog. - Apply incremental BFS/propagation only from affected sources/targets: for added edge (u→v), propagate new reachability from all sources that reach u to v and beyond; for removed edges, more complex — requires recomputing affected subgraphs or maintaining reference counts / path counts to know when a reachability path disappears. - Maintain path counts (number of distinct paths) for reachability; decrement counts on removes; only delete reachability when count hits zero. Store counts in distributed key-value (e.g., HBase, Cassandra) or as Parquet tables and update via Spark incremental jobs. - Periodic full re-compute to reconcile drift (nightly/weekly) depending on change volume.

Operational considerations:- Partition by source vertex to localize queries and updates.- Use sampling and monotonicity (most real graphs are sparse, low diameter) to optimize propagation.- Monitor job metrics (shuffles, memory spill, frontier sizes). Tune parallelism, broadcast small adjacency sets, and use compression.- For tight latency needs, combine materialized partial closure for hot nodes + online graph DB for cold paths.

Bottom line: avoid recursive CTE in a single RDBMS for this scale. Use distributed graph processing (Spark GraphX / Pregel-style) for batch/full closure, graph DBs for interactive traversal, and delta propagation with path counts for incremental updates.

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.