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

29 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

39 practiced

Edge-case question: If a CTE contains non-deterministic functions (e.g., random(), now()) and is referenced multiple times in a query, what behavior would you expect? How can this affect reproducibility of features for ML and what patterns prevent surprises?

MediumTechnical

35 practiced

You have a table `events(user_id, event_id, occurred_at)` that may have multiple duplicate rows per event_id due to ingestion issues. Write a query using a CTE and window function to deduplicate the table, keeping the latest `occurred_at` per `event_id`. Provide the DELETE or INSERT-SELECT approach (choose one) and explain why using ROW_NUMBER in a CTE is helpful.

EasyTechnical

39 practiced

Write a SQL query to find users who never made a purchase. Use the tables below and show three different approaches: (1) a correlated NOT EXISTS subquery, (2) NOT IN with a subquery, and (3) LEFT JOIN anti-join pattern. Explain which approach is safest regarding NULLs and performance.

Schemas:

users(user_id int, created_at timestamp)
transactions(transaction_id int, user_id int, amount numeric)

MediumTechnical

38 practiced

Given a production analytics query that joins a moderately-sized dimension table to an expensive CTE result, the join is slow. Propose three strategies (SQL-level and schema-level) to speed up the join and explain how each reduces cost.

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.