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

38 practiced

Explain whether CTEs are always materialized by the database engine and how materialization versus inlining affects performance. Discuss differences you are aware of across engines (Postgres behavior across versions, SQL Server, BigQuery, Snowflake) and how that knowledge should influence whether you choose a CTE, a subquery, or a temporary table in production.

Sample Answer

Short answer: CTEs are not always materialized — it depends on the engine and version. Whether a CTE is materialized (creates a temporary result) or inlined (treated like a macro/subquery and optimized along with the outer query) affects performance and semantics (especially for repeated use, volatility, or non-deterministic functions).

Key points and engine behavior:- PostgreSQL: - Pre-12: CTEs (WITH) are always materialized (optimization fence). That can be good for correctness/isolation but often slows queries because it prevents pushdown of predicates and join reordering. - Post-12: Planner inlines CTEs when safe (non-volatile, not marked MATERIALIZED), allowing optimization; you can still force materialization with WITH MATERIALIZED or prevent it with WITH NOT MATERIALIZED (PG 12+).- SQL Server: - CTEs are inlined into the outer query (they’re syntactic sugar) and are not materialized unless the optimizer chooses to spool for its own reasons; use temporary tables or table variables when you need persisted intermediate state.- BigQuery: - BigQuery generally inlines CTEs; however, using CREATE TEMP TABLE or the MATERIALIZED VIEW pattern can force materialization and sometimes improve performance when the sub-result is reused or expensive to compute.- Snowflake: - CTEs are inlined by default and optimized; Snowflake may materialize intermediate results internally for performance but you control persistence with temporary tables.

How materialization vs inlining affects performance:- Inlining allows the optimizer to push filters, reorder joins, and avoid extra I/O — typically faster for single-pass queries.- Materialization can be beneficial when: - The CTE is expensive and reused multiple times (compute once). - You need stable snapshot semantics (avoid repeated evaluation of non-deterministic expressions). - Breaking complex queries for readability reduces optimizer overload.- Materialization costs: extra storage, I/O, and preventing cross-query optimizations.

Practical guidance for production (Data Engineer perspective):- Prefer CTEs/subqueries for readability and when you want optimizer freedom; on Postgres <12, be cautious—materialization may hurt performance.- If an intermediate result is reused multiple times, or you need deterministic behavior, use a temporary table (CREATE TEMP TABLE) or explicit materialization. This gives control, lets you index or cluster, and aids reproducibility in pipelines.- Benchmark on your target engine and version. Use EXPLAIN/EXPLAIN ANALYZE and engine-specific hints (Postgres MATERIALIZED/NOT MATERIALIZED, BigQuery temp tables) to guide choices.- Automate tests: capture query plans and runtime metrics in CI so changes in optimizer versions don’t silently regress production jobs.

EasyTechnical

27 practiced

Write a SQL query (PostgreSQL) that uses a CTE to compute the average order amount per user and returns only users with average > 100. Table schema: orders(order_id INT, user_id INT, amount NUMERIC, created_at TIMESTAMP). Example input: user 1 amounts [150, 200], user 2 amounts [20, 30]. Expected output: user_id 1, avg_amt 175.0. Use a WITH clause and return columns (user_id, avg_amt).

Sample Answer

Approach: use a CTE to aggregate orders by user, compute the average amount, then select users whose average > 100. This keeps logic readable and reusable.

sql

WITH user_avgs AS (
  SELECT
    user_id,
    AVG(amount) AS avg_amt
  FROM orders
  GROUP BY user_id
)
SELECT
  user_id,
  avg_amt
FROM user_avgs
WHERE avg_amt > 100
ORDER BY user_id;

Key points:- AVG(amount) returns NUMERIC; Postgres preserves scale. If you want a float, cast: AVG(amount)::double precision.- GROUP BY user_id computes per-user aggregation inside the CTE, making the main query simple.- Filtering after aggregation (WHERE in outer query) ensures correct semantics (HAVING could also be used directly without a CTE).

Edge cases:- Users with no orders won’t appear. If you need them with NULL or 0, LEFT JOIN against a users table.- Null amounts are ignored by AVG; ensure data quality if nulls indicate issues.

MediumTechnical

37 practiced

For schema products(product_id, category_id, price) and categories(category_id, name), write a query that returns the top 3 most expensive products per category using LATERAL in Postgres, then rewrite it using a CTE or window function. Explain which version is more efficient and in what cases LATERAL is preferable or unsupported (e.g., MySQL pre-8, Snowflake variations).

Sample Answer

Approach: use LATERAL to pick top 3 products per category by joining each category to a correlated subquery. Then show an equivalent using window functions (ROW_NUMBER) and a CTE.

LATERAL version (Postgres):

sql

SELECT c.category_id, c.name, p.product_id, p.price
FROM categories c
JOIN LATERAL (
  SELECT product_id, price
  FROM products
  WHERE category_id = c.category_id
  ORDER BY price DESC
  LIMIT 3
) p ON true
ORDER BY c.category_id, p.price DESC;

CTE / window-function version:

sql

WITH ranked AS (
  SELECT p.product_id, p.category_id, p.price,
         ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS rn
  FROM products p
)
SELECT c.category_id, c.name, r.product_id, r.price
FROM categories c
JOIN ranked r ON r.category_id = c.category_id AND r.rn <= 3
ORDER BY c.category_id, r.price DESC;

Efficiency and trade-offs:- Window-function approach scans products once and assigns row numbers; typically more efficient for large tables because it avoids launching a separate subquery per category. It’s also broadly supported (Postgres, MySQL 8+, Snowflake).- LATERAL is handy when per-group logic is complex (e.g., calling a function, joining additional tables, applying different limits per category) because it allows fully correlated queries per row. It may be less efficient if categories are many and the DB executes many correlated subqueries.- Unsupported/considerations: MySQL pre-8 lacks window functions; it also doesn't support LATERAL until newer versions (MySQL 8+ supports lateral via LATERAL derived tables). Snowflake supports lateral (TABLE/FLATTEN variants) but syntax/optimizer differences may affect performance. Choose window functions for pure top-N per group; choose LATERAL when per-row correlation or procedural selection is needed.

EasyTechnical

33 practiced

Given table events(event_id BIGINT PRIMARY KEY, user_id INT, event_type TEXT, ts TIMESTAMP), write a SQL query using a CTE and window functions to deduplicate events per (user_id, event_type) keeping the earliest ts per group and returning the deduplicated rows (event_id, user_id, event_type, ts). Use standard SQL/Postgres syntax and explain how your query handles ties and NULL timestamps.

Sample Answer

Use a CTE with a window function to assign a row number per (user_id, event_type) ordered by the earliest timestamp. Break ties deterministically using event_id. Place NULL timestamps after real timestamps (NULLS LAST) so a non-NULL ts wins; if all are NULL the smallest event_id is kept.

sql

WITH ranked AS (
  SELECT
    event_id,
    user_id,
    event_type,
    ts,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type
      ORDER BY ts ASC NULLS LAST, event_id ASC
    ) AS rn
  FROM events
)
SELECT event_id, user_id, event_type, ts
FROM ranked
WHERE rn = 1;

Explanation:- PARTITION BY groups rows per (user_id, event_type).- ORDER BY ts ASC NULLS LAST picks the earliest non-NULL timestamp first; NULLs are treated as latest.- event_id ASC is a deterministic tie-breaker when ts values are identical (or all NULL).- ROW_NUMBER() returns 1 for the chosen row; filtering rn = 1 yields the deduplicated set.Edge cases: if you prefer keeping NULLs when present, change NULLS LAST → NULLS FIRST. If you want arbitrary tie handling, use MIN(ts) with DISTINCT ON as an alternative.

HardTechnical

38 practiced

In Spark SQL you see a multi-stage iterative algorithm implemented with chained CTEs that leads to multiple Spark jobs and recomputation. Explain how Spark's Catalyst optimizer handles CTEs, why repeated references might trigger recomputation, and show how you would rework the code using DataFrame.persist(), checkpoint(), or caching to avoid repeated work. Provide short PySpark snippets illustrating the fix.

Sample Answer

Catalyst treats SQL CTEs (WITH ... AS ...) as logical query expressions. By default CTEs are inlined — their logical plan is substituted where referenced. If a CTE is referenced multiple times across chained iterations, Catalyst will re-expand and re-optimize each reference; at physical execution this can produce multiple Spark jobs and recomputation of the same upstream work unless the planner can fuse or reuse subplans. Spark does not automatically materialize repeated logical subplans.

Fix: materialize the intermediate result so repeated references reuse the computed DataFrame. Options:- persist()/cache(): keep data in memory/disk for fast reuse within the same Spark application.- checkpoint(): write to reliable storage and cut lineage (useful if lineage is long or you want fault-tolerant truncation).- write/read (parquet) as an explicit materialization.

Example: original SQL-like iterative CTE (conceptual) repeatedly referencing "iter" causes recompute. Convert to DataFrame and persist/checkpoint.

python

# assume spark session exists and df0 is initial DataFrame
iter_df = df0

for i in range(5):
    # compute next iteration from iter_df (previous)
    next_df = (iter_df
               .filter("some_condition")
               .withColumn("score", expr("...")))

    # Materialize to avoid recomputation on next loop or multiple joins
    next_df = next_df.persist(StorageLevel.MEMORY_AND_DISK)
    # Optionally checkpoint to cut lineage (set checkpoint dir earlier)
    # spark.sparkContext.setCheckpointDir("/tmp/checkpoints")
    # next_df = next_df.checkpoint(eager=True)

    # trigger materialization
    next_df.count()

    # use next_df in multiple places without re-running upstream
    result_a = next_df.join(other_a, "id").agg(...)
    result_b = next_df.filter("...").select(...)

    # persist results or write out if long-lived
    result_a.write.mode("overwrite").parquet(f"/tmp/output/a_iter_{i}")

    # prepare for next iteration: unpersist previous
    iter_df.unpersist()
    iter_df = next_df

Notes and best practices:- Call an action (count(), write()) after persist/checkpoint to force materialization.- Use checkpoint(eager=True) to cut lineage; good when iterations create deep plans causing driver/executor pressure.- Prefer MEMORY_AND_DISK for large data; avoid over-caching small temporary results.- Unpersist intermediate DataFrames when no longer needed to free memory.- For SQL workloads, convert CTEs to temporary views and persist the DataFrame backing the view to avoid repeated replan/recompute.

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.