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 behavioral and performance differences between `WHERE id IN (subquery)` and an equivalent `INNER JOIN` in a BI reporting query. Use an example where the lookup table is significantly smaller than the fact table and discuss duplicate rows, null handling, and when each approach might be better for a dashboard.
MediumTechnical
0 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).
MediumTechnical
0 practiced
Discuss the benefits and pitfalls of using CTEs for readability and maintainability in BI SQL code. Provide two real-world examples: one where CTEs make a long metric pipeline clear, and one where CTEs hide expensive operations and caused production issues.
MediumTechnical
0 practiced
In Snowflake, queries using many CTEs sometimes cause memory spikes and timeouts. List configuration and SQL-level approaches to mitigate this, including warehouse sizing, clustering keys, query rewriting, and use of transient or materialized tables. Explain trade-offs in cost and complexity.
EasyTechnical
0 practiced
Explain what a Common Table Expression (CTE) is in SQL and how it differs from a subquery. Include a concise non-recursive CTE example using `WITH` and a correlated subquery example, and describe three BI-centric use cases where you'd prefer a CTE over a subquery when building dashboards or reports.
Unlock Full Question Bank
Get access to hundreds of CTEs & Subqueries interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.