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.

HardSystem Design

28 practiced

Design an architecture for near-real-time dashboard metrics where recursive CTEs are known to be expensive on large hierarchies. Outline alternative data pipelines (streaming ingestion, incremental aggregates, materialized views, precomputation) and explain trade-offs for cost, freshness, and complexity for a BI team.

MediumTechnical

39 practiced

A query repeatedly references the same expensive subquery multiple times within a single report. Describe and show how to optimize it by materializing intermediate results with a temp table or by refactoring into a single CTE. Provide example SQL in PostgreSQL and explain trade-offs (concurrency, locking, storage).

EasyTechnical

31 practiced

You have an `events(user_id, event_type, occurred_at)` table that may contain duplicate event rows. Write a SQL query (Postgres or SQL Server) using a CTE and window functions to keep only the most recent event per user and event_type. Explain how your approach handles ties and NULL occurred_at values.

EasyTechnical

37 practiced

Write SQL to pivot monthly sales for the last 6 months into columns using a CTE and conditional aggregation in Postgres. Provide the `sales(order_id, product_id, amount, occurred_at)` schema assumption and show how to handle months with no sales (fill with zero).

HardTechnical

37 practiced

You find a complex report query made of nested subqueries and multiple CTEs that is slow. Rewrite the conceptual query using window functions to collapse redundant aggregations into a single pass and explain trade-offs in maintainability versus performance. Provide a small sample transformation example.

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.