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

Convert this correlated subquery into an equivalent query that uses a CTE and a JOIN. Original:

sql

SELECT c.customer_id, c.name,
  (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;

Rewrite using a CTE that computes last_order_date per customer and then joins to customers. Explain why this can be more efficient.

MediumTechnical

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

EasyTechnical

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

MediumTechnical

0 practiced

Solve the islands-and-gaps problem using CTEs: given user_activity(user_id, activity_date), return consecutive date ranges (start_date, end_date) for each user where dates are continuous. Show SQL and explain the grouping technique you used.

MediumTechnical

0 practiced

Given a large events table, write a SQL approach using CTEs to compute 7-day retention rates by weekly cohort (cohort week = first event week). Describe performance considerations and how you'd optimize to limit data scanned when computing retention over a three-month window.

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.