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

37 practiced

Compare CTEs and temporary tables for building BI reports. Cover lifetime, reusability across multiple queries, need for indexes, suitability for large intermediate datasets, and typical use-cases where temp tables outperform CTEs.

HardTechnical

39 practiced

You observe inconsistent totals when running logically equivalent queries: one uses multiple CTEs with window functions and the other is a flattened version. Explain possible causes of non-deterministic behavior (ordering, parallelism, missing ORDER BY) and propose fixes to guarantee deterministic results in analytics reports.

EasyTechnical

35 practiced

Using a CTE, compute a 7-day rolling average of daily active users (DAU) from an events table with schema:

sql

events(user_id INT, event_date DATE)

Instructions: first aggregate unique users per date in a CTE, then compute the 7-day rolling average. Explain how you handle missing dates in the range.

MediumTechnical

35 practiced

You have a scalar subquery (tax rate per region) used multiple times in a report. Show how to compute this scalar once using a CTE and CROSS JOIN to apply it in calculations across rows. Provide a small example with region-based tax rates and revenue calculation.

EasyTechnical

34 practiced

Refactor the following query into one that uses a CTE for readability and to avoid repeating computation. Original:

sql

SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'completed') AS completed_orders
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 5;

Rewrite using a CTE and explain readability or performance benefits.

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.