Common Table Expressions and Subqueries Questions

Covers writing and structuring complex SQL queries using Common Table Expressions and subqueries, including when to prefer one approach over another for readability, maintainability, and performance. Candidates should be able to author WITH clauses to break multi step logic into clear stages, implement recursive CTEs for hierarchical data, and use subqueries in SELECT, FROM, and WHERE clauses. This topic also includes understanding correlated versus non correlated subqueries, how subqueries interact with joins and window functions, and practical guidance on choosing CTEs, subqueries, or joins based on clarity and execution characteristics. Interviewers may probe syntax, typical pitfalls, refactoring nested queries into CTEs, testing and validating each step of a CTE pipeline, and trade offs that affect execution plans and index usage.

HardTechnical

27 practiced

You have a 1TB transactions table and a reporting query that joins it to lookup tables and uses multiple CTEs. The query does full table scans. Provide a prioritized set of optimizations (indexes, partitioning, predicate pushdown, pre-aggregation, approximate algorithms) and a testing plan to measure the effectiveness of each change.

EasyTechnical

31 practiced

Describe how using a CTE pipeline (several WITH blocks each feeding the next) helps with debugging and incremental validation of complex transformations for a dashboard. Provide a short example outline of 3 stages (cleaning, deduping, aggregation) you would implement as CTEs.

HardTechnical

27 practiced

Implement sessionization using SQL and CTEs: assign session_id per user where sessions are defined as sequences of events separated by less than 30 minutes. Table:

-- events(user_id int, occurred_at timestamp)

Return: user_id, session_start, session_end, event_count for each session. Explain window-function steps and edge cases.

MediumTechnical

45 practiced

Write SQL (PostgreSQL) that computes year-over-year revenue growth per product. Use a CTE to compute yearly revenue per product and then join the CTE to itself to compute growth percentage for each product-year pair.

MediumTechnical

35 practiced

Explain how CTEs interact with window functions. Provide an example where you compute a row_number partitioned by customer and then filter for top purchases per customer. Why is it often necessary to compute the windowed column inside a CTE before filtering?

Unlock Full Question Bank

Get access to hundreds of Common Table Expressions and Subqueries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.