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

0 practiced

Describe the difference between a correlated subquery and a non-correlated subquery. Using a customers and orders schema, write two example queries: (A) select customers whose order total exceeds their own average order amount (correlated), and (B) select customers with at least one order above the global average order amount (non-correlated).

MediumTechnical

0 practiced

Write a recursive CTE to compute the transitive closure (reachability) up to 4 hops in a follows table representing a directed social graph. Schema:

sql

follows(follower INT, followee INT)

Return reachability_count per user and shortest known path length for each reachable user, preventing revisiting the same node within a path.

HardTechnical

0 practiced

You manage a large BI SQL codebase. Propose a plan to detect and prevent common CTE anti-patterns (e.g., unnecessary materialization, SELECT *, deep recursion) using static analysis and CI. Provide example linter rules, code-review checklists, and metrics to track improvement over time.

EasyTechnical

0 practiced

Demonstrate chaining multiple CTEs to transform raw event data into a final report. Use this simplified flow: raw_events -> filtered_events -> daily_user_counts -> top_users. Provide SQL for each step and the final SELECT that returns the top 10 users by 30-day active count.

HardTechnical

0 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.

Join thousands of developers preparing for their dream job.