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.
EasyTechnical
38 practiced
Given a table `orders(order_id, customer_id, amount, occurred_at)` write a PostgreSQL query using a non-recursive CTE to compute total revenue and order_count per customer for the current quarter and return top 10 customers by revenue. Explain any assumptions you make about time functions and NULLs.
HardTechnical
38 practiced
Construct a Postgres SQL statement that uses a recursive CTE to flatten a deeply nested JSON structure stored in `payload jsonb` into normalized rows. Provide a short sample JSON example inline and discuss memory and performance considerations for large JSON blobs.
MediumTechnical
35 practiced
Given a correlated subquery used to fetch the latest order date per customer (e.g., in WHERE or SELECT), rewrite it using a CTE and window functions to improve performance. Provide both the original correlated subquery example and the refactored CTE-based query and explain why the latter can be faster for BI reports.
MediumTechnical
35 practiced
Write a recursive CTE to traverse an employee hierarchy stored in `employees(id, manager_id, name)` and produce for each employee their chain-of-command path and depth. Limit recursion to depth 10 and avoid infinite loops if there are cycles. Explain how you would expose this as a table for BI consumers.
HardTechnical
35 practiced
Design a testing and CI strategy for SQL code that contains complex CTEs used by dashboards. Include unit tests, regression tests, test data patterns, and how to validate performance regressions automatically during deployments.
Unlock Full Question Bank
Get access to hundreds of CTEs & Subqueries interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.