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

32 practiced

When embedding analytic SQL in BI tools like Tableau or Power BI, what are the implications of using complex multi-CTE queries versus pushing logic into database views or materialized tables? Discuss maintainability, performance, and how dashboard filters should be pushed down.

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.

HardTechnical

26 practiced

You maintain a complex multi-step analytics pipeline that currently uses inline CTEs. Discuss the trade-offs between leaving it as a single CTE-based query, breaking it into temporary staging tables, or using materialized views. Consider maintainability, performance, ability to add indexes, and execution engines such as Postgres, BigQuery, and Redshift.

MediumTechnical

34 practiced

Correlated subqueries that include aggregate functions in the SELECT clause can be expensive when executed per row. Describe how to refactor a query that uses SELECT (SELECT SUM(x) FROM t WHERE t.group_id = g.id) into a CTE + JOIN pattern and explain performance benefits.

HardTechnical

54 practiced

In PostgreSQL you can use LATERAL joins. Given purchases(user_id INT, product_id INT, purchased_at TIMESTAMP) and affinities(product_id INT, recommended_product_id INT, score FLOAT), write a query that uses a CTE to get each user's latest purchased product and then a LATERAL join to return the top recommended product for that latest purchase.

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.