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
Given the table `transactions(transaction_id, user_id, amount numeric, occurred_at timestamp)`, write a SQL query (in PostgreSQL) using a CTE to compute each user's lifetime value (sum(amount)) over the last 365 days, and return the top 10 users by lifetime value. Include the CTE and the final SELECT.
MediumTechnical
0 practiced
Discuss how CTEs can impact query performance. Include examples of situations where CTEs may cause repeated work or materialization, and describe simple methods (EXPLAIN, ANALYZE, temporary tables, materialized views) to measure and mitigate performance issues when preparing features for ML pipelines.
MediumTechnical
0 practiced
Explain the difference between scalar subqueries used in the SELECT list and aggregates joined via GROUP BY. Provide an example where a scalar subquery can be replaced by a join with aggregation and discuss performance implications on large datasets.
MediumTechnical
0 practiced
Given this query used to compute top 3 purchases per user, implement it using a CTE and ROW_NUMBER():Tables: `purchases(user_id, purchase_id, amount, purchased_at)`Desired output columns: user_id, purchase_id, amount, rank (1 is highest amount). Return up to top 3 per user.
EasyTechnical
1 practiced
In many SQL dialects you can chain multiple CTEs: `WITH a AS (...), b AS (...), c AS (...) SELECT ...`. Explain CTE scoping rules: can a CTE reference another CTE defined earlier in the WITH clause? Can the outer query see variables from the CTE? Provide a short example demonstrating chained CTEs.
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.