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

0 practiced

Write a SQL query to identify cycles in a directed graph of items using a recursive CTE. Schema:

-- edges(from_id int, to_id int)

Return any nodes that are part of a cycle. Explain how your recursive CTE prevents infinite recursion and how it identifies repeated nodes in a path.

HardTechnical

0 practiced

You're code-reviewing a colleague's query that uses many nested CTEs and performs poorly. How would you provide constructive feedback focused on performance, readability and maintainability? List concrete suggestions you might make and how you'd offer follow-up support.

MediumTechnical

0 practiced

Explain in detail how correlated subqueries are evaluated and why they can be slow. Can the optimizer transform a correlated subquery into a join? Provide scenarios where that is possible and where it is not.

EasyTechnical

0 practiced

Explain what a Common Table Expression (CTE) is in SQL and show the basic WITH-clause syntax. Include a short example that computes total_sales per region from a sales table and a brief note about recursive CTEs. Example schema:

-- sales(order_id int, region text, amount numeric, order_date date)

Show a simple CTE example and mention, at a high level, how a CTE differs from a subquery and a temporary table.

HardTechnical

0 practiced

Explain what an 'optimization fence' is with respect to CTEs and why it can be both helpful and harmful. Give examples of when you'd intentionally force materialization (for correctness or performance) and when you'd want inlining/pushdown.

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.