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

0 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

0 practiced

Identify which of the following subqueries are correlated and which are not. Explain your reasoning and the effect on execution.

Snippet A:

SELECT p.id, p.name
FROM products p
WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category);

Snippet B:

SELECT c.customer_id
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders WHERE order_date > '2024-01-01');

MediumTechnical

0 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.

EasyTechnical

0 practiced

Explain what a Common Table Expression (CTE) is in SQL and show the basic WITH-clause syntax. Include a short example that computes total_sales per region from a sales table and a brief note about recursive CTEs. Example schema:

-- sales(order_id int, region text, amount numeric, order_date date)

Show a simple CTE example and mention, at a high level, how a CTE differs from a subquery and a temporary table.

MediumTechnical

0 practiced

You have a complex nested query that computes customer cohorts and churn rates using multiple nested subqueries. Refactor this into a chain of CTEs (cohort assignment, purchases per cohort, churn calculation) and describe how you'd add unit tests to validate each CTE step.

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.