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

28 practiced

Given an `employees(id, manager_id, name)` table, write a recursive CTE that returns all direct and indirect reports for a given manager_id = 42, including the depth (distance) from the manager. Explain how you prevent cycles and how you would limit maximum depth.

MediumTechnical

32 practiced

You're building a feature engineering SQL pipeline with chained CTEs: one to filter raw events, one to aggregate per-user stats, and one to normalize features. Explain how you would structure these CTEs for readability and testability, and discuss when you would instead use temporary/intermediate tables or materialized views for performance and reproducibility.

MediumTechnical

28 practiced

What is a recursive CTE? Provide a concise, real-data-science oriented example: write a recursive CTE that generates a series of dates (one per day) between two given dates (start_date and end_date) and explain how it terminates. Use PostgreSQL syntax (WITH RECURSIVE).

HardTechnical

33 practiced

You must compute the 90th percentile session length per user. The `sessions(user_id, start_ts, end_ts)` table is available. Write a SQL query using CTE(s) and a window or ordered-set aggregate to compute the per-user 90th percentile session length (in seconds). Explain your choice of function and any portability concerns.

HardSystem Design

54 practiced

You're designing an ETL step in which multiple downstream ML features need the same expensive aggregation. The initial implementation uses a single CTE referenced in several places and the job is slow. Describe 4 different strategies to reduce runtime and avoid recomputation in the ETL pipeline, including trade-offs for each (temp tables, materialized views, caching, job decomposition).

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.