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.

MediumTechnical

26 practiced

Explain how database statistics, cardinality estimation, and indexes influence the execution of queries that use subqueries in WHERE clauses versus equivalent JOINs or CTE-based plans. Provide an example where poor statistics lead to a bad plan and how you would remedy it as a BI analyst with limited DBA privileges.

Sample Answer

Database statistics and cardinality estimates drive the optimizer’s cost model; indexes provide access paths. When a WHERE clause contains a subquery (IN/EXISTS or correlated), the optimizer must estimate how many rows that subquery will return (cardinality). If stats are accurate it can choose an efficient nested-loop, hash, or merge strategy, or transform the subquery into a join/CTE during optimization. If stats are stale or missing, the optimizer may vastly under- or over-estimate cardinality and pick a bad plan (e.g., full table scan, wrong join order, or an expensive nested-loop instead of a hash join).

Example of a bad plan:- Fact table orders (10M rows) joined to customers via a correlated EXISTS that references customer.region.- Customer table has highly skewed region distribution but no histogram; optimizer assumes uniform distribution and expects few matching customers per region. It chooses nested-loop scanning customers for each order → massive I/O and timeouts.

How I’d remediate as a BI analyst with limited DBA privileges:1. Reproduce and capture evidence: save execution plans and runtime stats (EXPLAIN/EXPLAIN ANALYZE or execution plan XML), plus sample query runtime.2. Query rewrite: replace correlated subquery with an explicit INNER JOIN or a CTE that pre-aggregates/filter customers. Example: - Bad: WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.region = 'X') - Better: WITH cust_region AS (SELECT id FROM customers WHERE region='X') SELECT ... FROM orders o JOIN cust_region c ON o.customer_id = c.id; This lets the optimizer re-evaluate join strategies and can avoid nested loops.3. Local/materialized workaround: create a small indexed temp table or materialized table for the filtered customer set (CREATE TABLE #cust_region ...; CREATE INDEX on it). Many systems allow creating temp tables without DBA rights; an index on the temp table helps join selectivity.4. Use plan-stabilizing hints when supported (OPTION (RECOMPILE) or FORCESEEK/FORCE INDEX) sparingly — include the captured plan and justification when requesting approval.5. If rewrite/temps still slow, open a concise ticket for DBAs attaching the plan and recommending statistics refresh or histogram creation. Provide exact commands they can run (e.g., ANALYZE, UPDATE STATISTICS) and demonstrate performance improvement from your rewritten query to justify the change.

Key reasoning: accurate stats let the optimizer choose the right join algorithm and index; when you can’t change server-level stats, changing the query shape (pre-filtering via CTE/temp tables, adding local indexes) or using controlled hints gives practical performance gains and evidence to get DBA support.

MediumTechnical

28 practiced

Provide a refactor of a SQL query that uses multiple repeated CTEs with identical logic into a single CTE that is referenced multiple times. Discuss whether many engines will recompute that CTE each reference or reuse materialized result, and how you would enforce reuse if needed.

Sample Answer

Situation: You have the same CTE logic repeated multiple times in a query — e.g. compute filtered events or an aggregated customer snapshot — and want to rewrite it once and reference it multiple times.

Refactor (example):

-- before: repeated identical CTEs A1, A2...

sql

WITH recent_orders AS (
  SELECT user_id, SUM(amount) total, COUNT(*) cnt
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY user_id
),
user_metrics AS (
  SELECT u.id, u.signup_date, r.total
  FROM users u
  LEFT JOIN recent_orders r ON r.user_id = u.id
),
-- then repeated: another CTE reusing same recent_orders logic
recent_orders_2 AS (
  SELECT user_id, SUM(amount) total, COUNT(*) cnt
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY user_id
)
SELECT ...

-- after: single CTE referenced multiple times

sql

WITH recent_orders AS (
  SELECT user_id, SUM(amount) total, COUNT(*) cnt
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY user_id
),
user_metrics AS (
  SELECT u.id, u.signup_date, r.total
  FROM users u
  LEFT JOIN recent_orders r ON r.user_id = u.id
),
churn_candidates AS (
  SELECT u.id
  FROM users u
  LEFT JOIN recent_orders r ON r.user_id = u.id
  WHERE COALESCE(r.cnt,0) = 0
)
SELECT um.*, (CASE WHEN c.id IS NOT NULL THEN 1 ELSE 0 END) as is_churn
FROM user_metrics um
LEFT JOIN churn_candidates c ON c.id = um.id;

Will the CTE be recomputed each reference?- Many engines treat WITH-CTE as syntactic inlineable subqueries and will re-optimize/inline them; this can lead to recomputation for each reference. Examples: - PostgreSQL historically treated CTEs as optimization fences (materialized) until v12; from v12 it inlines CTEs by default unless MATERIALIZED is specified. - Most commercial engines (BigQuery, Redshift, Snowflake, SQL Server) typically inline CTEs and may recompute them per reference unless the optimizer decides otherwise.- Behavior is engine-specific and depends on optimizer complexity and query plan.

How to enforce reuse (materialization) if needed:1. Use engine-supported MATERIALIZED hint: - PostgreSQL 12+: WITH recent_orders AS MATERIALIZED ( ... ) forces materialization.2. Create a temporary or staging table:

sql

CREATE TEMP TABLE tmp_recent_orders AS
SELECT ... ;
-- use tmp_recent_orders in downstream queries

3. Use a persistent/materialized view if reuse across sessions is desired:

sql

CREATE MATERIALIZED VIEW mv_recent_orders AS SELECT ...;
REFRESH MATERIALIZED VIEW mv_recent_orders;

4. In some engines use query hints or result caching (engine-specific).

Trade-offs and guidance:- Materializing avoids repeated computation and can dramatically reduce runtime for expensive aggregations, but increases IO, storage, and may serve stale data unless refreshed.- Prefer single CTE for readability and let the optimizer decide; if profiling shows recomputation, enforce materialization via MATERIALIZED, temp tables, or materialized views.- Always validate with EXPLAIN/EXPLAIN ANALYZE and measure before/after.

This approach reduces duplication, clarifies intent for downstream reporting (dashboards), and gives options to enforce reuse when performance profiling requires it.

HardTechnical

28 practiced

Explain the implications of using ORDER BY inside CTEs and how some databases may ignore ORDER BY in non-final query blocks. Provide an example where relying on ORDER BY inside a CTE produces incorrect assumptions for a downstream LIMIT, and describe the correct approach.

Sample Answer

Using ORDER BY inside a CTE can be misleading: SQL only guarantees row ordering for the final result set. Many databases ignore ORDER BY in non-final query blocks (subqueries/CTEs) unless it's paired with LIMIT/TOP or used in a window function. Relying on an ORDER BY inside a CTE to influence a downstream LIMIT is therefore unsafe and can produce incorrect assumptions.

Example (unsafe assumption):

sql

WITH top_sales AS (
  SELECT customer_id, total_amount
  FROM sales
  ORDER BY total_amount DESC   -- NOT guaranteed to be preserved
)
SELECT *
FROM top_sales
LIMIT 5;  -- assumes this returns top 5 by total_amount — not guaranteed

Why this fails:- The optimizer can ignore ordering inside the CTE and reorder or inline it.- PostgreSQL used to treat CTEs as an optimization barrier (pre-12) but now may inline them unless MATERIALIZED is specified; other engines behave differently.

Correct approaches:1) Order in the final query:

sql

WITH sales_cte AS (
  SELECT customer_id, total_amount FROM sales
)
SELECT customer_id, total_amount
FROM sales_cte
ORDER BY total_amount DESC
LIMIT 5;

2) Use a deterministic window function inside the CTE to mark ranks, then filter (portable and robust):

sql

WITH ranked_sales AS (
  SELECT customer_id, total_amount,
         ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS rn
  FROM sales
)
SELECT customer_id, total_amount
FROM ranked_sales
WHERE rn <= 5
ORDER BY rn;

Recommendation: For BI reporting, always apply ORDER BY in the final projection or use ROW_NUMBER()/RANK() when you need a stable “top N” from a subquery/CTE. This avoids optimizer surprises and makes your dashboards reliable across databases.

HardTechnical

37 practiced

Explain transactional and concurrency implications of creating and using temporary tables versus CTEs in a reporting pipeline that both analysts and scheduled jobs run concurrently. Include behavior differences across at least two engines (e.g., Postgres temp tables are session-scoped).

Sample Answer

Short answer: CTEs (WITH ...) are statement-scoped, non-persistent query expressions; they produce no cross-session state and are safe for concurrency. Temporary tables create session-scoped (or connection-scoped) objects that persist across statements in that session, can be indexed/materialized, and therefore have different transactional and concurrency effects — both benefits and pitfalls — which vary by engine.

Key differences and transactional implications- Scope & visibility - Postgres: TEMP/TEMPORARY tables are visible only to the session that created them and are dropped at session end. They are regular tables from an access/lock/transaction point of view but isolated to your session. - MySQL (InnoDB): TEMPORARY tables are also session-scoped, but DDL in MySQL issues implicit commits; CREATE/DROP TEMPORARY TABLE will commit the current transaction, so you can’t roll them back the way you can in Postgres. - Snowflake: TEMPORARY tables persist for the session; Snowflake’s transactions are different (no implicit commit on CREATE TABLE), and temporary tables are isolated to the session. - SQL Server: local temp tables (#name) are connection-scoped; global (##) are shared across sessions.

- Transactionality - Postgres: CREATE TEMP TABLE and subsequent DML are transactional — you can roll back inserts into a temp table if they occurred in the transaction that’s rolled back (but if you create the temp table in one transaction and insert in another, the CREATE may be rolled back with the transaction that created it). - MySQL: CREATE TEMPORARY TABLE issues implicit commit → you lose transactional atomicity around DDL; dangerous for multi-step transactional pipelines. - CTEs: are part of a single statement and don’t interact with transaction boundaries; they don’t require DDL and carry no DDL-induced commits.

- Concurrency & collisions - CTEs: no collisions; each concurrent job/analyst run is independent. - Temp tables: session-scoped prevents other sessions from seeing your temp table, but collisions can occur if: - Connection pooling reuses a session between different users/jobs (leftover temp objects). - Naming is not unique — two jobs started in the same session or using shared connections may conflict. - Global temp tables (SQL Server) or poorly-named temp tables can cause cross-job interference.

- Performance & materialization - CTEs are logical; many engines inline or optimize them away. Some engines (or complex queries) may materialize CTEs which can help or hurt performance. - Temp tables allow indexing, statistics gathering, and incremental multi-step processing — useful for heavy transformations or when you need to reuse intermediate results across multiple statements.

Practical recommendations for a BI environment- Prefer CTEs for ad-hoc analyst queries and scheduled reports that are single-statement transformations: they are concurrency-safe and simpler.- Use temp tables when you need: - Multi-statement pipelines - Indexes on intermediates for performance - Large intermediate results reused across queries But: - Ensure unique naming (include PID, session_id, job_id, or UUID) to avoid collisions. - Explicitly DROP temp tables at job end; avoid relying on session termination when using pooled connections. - On MySQL, avoid temp-table DDL inside transactional work because CREATE/DROP will commit; restructure so DDL happens outside critical transactions. - For Postgres, be aware temp tables are transactional — creating inside a transaction that is rolled back removes the table — design creations accordingly. - Configure connection pools to avoid unexpected session-sharing between analyst tools and scheduled jobs (or use ephemeral connections for scheduled jobs).- Consider engine-specific alternatives: use transient/staging schemas or dedicated per-job schemas in data warehouses (Snowflake) to get isolation + performance without session-scoped pitfalls.

Example naming pattern: temp_results_{jobname}_{timestamp}_{short_uuid}

Summary: CTEs are safest for concurrency and simplicity; temp tables offer control and performance for multi-step jobs but require careful handling of transactions, connection pooling, naming, and engine-specific DDL behavior (notably Postgres transactional DDL vs MySQL implicit commits).

MediumTechnical

36 practiced

Demonstrate how to use `LATERAL` (or `CROSS APPLY` in SQL Server) to replace a correlated subquery in the FROM clause. Use a real example with `users` and `payments` where you need the latest payment per user and three most recent payments as JSON array. Explain advantages in BI reporting queries.

Sample Answer

Situation: In BI reporting you often need the latest payment per user plus a compact history (e.g., three most recent payments) for each user without expensive correlated aggregation or multiple passes.

Approach: Use LATERAL (Postgres) or CROSS APPLY (SQL Server) to run a small per-row subquery that can reference the outer row efficiently. This replaces correlated subqueries in SELECT/FROM and is easier to read/optimize.

Postgres example (latest payment + 3-most-recent as JSON):

sql

SELECT
  u.id AS user_id,
  u.email,
  p_latest.*,
  p_recent.payments_json
FROM users u
LEFT JOIN LATERAL (
  SELECT id AS payment_id, amount, paid_at
  FROM payments
  WHERE user_id = u.id
  ORDER BY paid_at DESC
  LIMIT 1
) p_latest ON true
LEFT JOIN LATERAL (
  SELECT json_agg(row_to_json(t) ORDER BY t.paid_at DESC) AS payments_json
  FROM (
    SELECT id, amount, paid_at
    FROM payments
    WHERE user_id = u.id
    ORDER BY paid_at DESC
    LIMIT 3
  ) t
) p_recent ON true;

SQL Server equivalent (use CROSS APPLY and FOR JSON PATH):

sql

SELECT
  u.id AS user_id,
  u.email,
  p_latest.payment_id,
  p_latest.amount,
  p_latest.paid_at,
  p_recent.payments_json
FROM users u
OUTER APPLY (
  SELECT TOP (1) p.id AS payment_id, p.amount, p.paid_at
  FROM payments p
  WHERE p.user_id = u.id
  ORDER BY p.paid_at DESC
) p_latest
OUTER APPLY (
  SELECT (
    SELECT TOP (3) id, amount, paid_at
    FROM payments p2
    WHERE p2.user_id = u.id
    ORDER BY paid_at DESC
    FOR JSON PATH
  ) AS payments_json
) p_recent;

Why this helps BI reporting:- Performance: The planner can optimize lateral/cross-apply better than repeated correlated subqueries; it avoids full scans and allows index use per user (use index on payments(user_id, paid_at DESC)).- Readability: Clear separation of "latest" and "recent list" logic makes SQL easier to maintain for dashboard queries.- Single-pass result: Produces denormalized rows with nested JSON ready for BI tools (reduced client-side joins and processing).- Flexibility: Easy to add more lateral subqueries (e.g., aggregates, flags) without complex GROUP BYs.

Edge notes:- Ensure proper indexes on payments(user_id, paid_at) for performance.- For very large user sets consider batching or pre-aggregated materialized views for dashboards refreshed periodically.

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.