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
32 practiced
Using sales(order_id, product_id, quantity, price, order_date), write SQL (with CTEs) to compute monthly revenue per product, then identify products that increased revenue by more than 30% month-over-month for at least two consecutive months. Explain how you handle months with zero revenue.
MediumTechnical
34 practiced
You have a query that chains many small CTEs but only uses the final result once. Discuss trade-offs of leaving the CTE chain for readability versus flattening the query into a single statement for potential optimization. What criteria do you use to decide?
HardTechnical
39 practiced
Write an optimized SQL query (using CTEs) to compute the top 1000 product pairs by co-purchase frequency from order_items(order_id, product_id). Discuss performance optimizations (sharding by order_id, using hashing, sampling), and describe approximate alternatives if exact computation is infeasible.
MediumTechnical
30 practiced
Write SQL that assigns session_id to events for each user where a new session starts if the gap between consecutive events is greater than 30 minutes. Schema: events(user_id INT, event_ts TIMESTAMP). Use a CTE and window functions to compute session_id, session_start, and session_end.
HardTechnical
38 practiced
Propose a safe upsert (insert-or-update) pattern using CTEs for a database that doesn't support MERGE. The business use-case is periodic bulk imports of cleaned customer records; ensure the approach avoids race conditions under concurrent loads. Provide SQL and explain transaction/isolation choices.
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.