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 difference between a correlated subquery and a non-correlated subquery. Using a customers and orders schema, write two example queries: (A) select customers whose order total exceeds their own average order amount (correlated), and (B) select customers with at least one order above the global average order amount (non-correlated).

EasyTechnical

0 practiced

Compare CTEs and temporary tables for building BI reports. Cover lifetime, reusability across multiple queries, need for indexes, suitability for large intermediate datasets, and typical use-cases where temp tables outperform CTEs.

MediumTechnical

0 practiced

Given an EXPLAIN ANALYZE output that shows a CTE being materialized and large disk-based sorts causing poor performance, identify the key signs in the plan and list specific remediation steps you would try to improve query runtime (both short-term and long-term).

MediumTechnical

0 practiced

You have a scalar subquery (tax rate per region) used multiple times in a report. Show how to compute this scalar once using a CTE and CROSS JOIN to apply it in calculations across rows. Provide a small example with region-based tax rates and revenue calculation.

EasyTechnical

0 practiced

Write a SQL query using a Common Table Expression (CTE) to return the top 5 customers by total revenue from the orders table. Use the following table schemas:

sql

customers(customer_id INT PRIMARY KEY, name VARCHAR)
orders(order_id INT PRIMARY KEY, customer_id INT, order_date DATE, total_amount DECIMAL)

Requirements: create a CTE named customer_revenue that computes total revenue per customer, then select top 5 by revenue. Use ANSI SQL and state any tie-breaking assumptions you make.

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.