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

Write a SQL query (PostgreSQL) that uses a CTE to compute the average order amount per user and returns only users with average > 100. Table schema: orders(order_id INT, user_id INT, amount NUMERIC, created_at TIMESTAMP). Example input: user 1 amounts [150, 200], user 2 amounts [20, 30]. Expected output: user_id 1, avg_amt 175.0. Use a WITH clause and return columns (user_id, avg_amt).

MediumTechnical

0 practiced

You have the following slow query where a heavy aggregation on sales is repeated three times: SELECT p.product_id, (SELECT SUM(amount) FROM sales s WHERE s.product_id = p.product_id) AS total_sold, (SELECT COUNT(*) FROM sales s WHERE s.product_id = p.product_id) AS order_count, (SELECT AVG(amount) FROM sales s WHERE s.product_id = p.product_id) AS avg_price FROM products p; Rewrite this query using a single CTE that computes the aggregated sales per product once and is reused in the outer SELECT. Provide the optimized SQL.

MediumTechnical

0 practiced

Explain when using EXISTS (a correlated subquery) is preferable to a LEFT JOIN when you want to check whether related rows exist without duplicating the left-hand rows. Provide an example with orders and payments where LEFT JOIN might multiply rows and show the equivalent EXISTS version. Discuss performance considerations and selectivity implications.

MediumTechnical

0 practiced

You maintain a partitioned events table partitioned by day. Write a SQL query using a CTE to find partitions that have not received new events in the last 90 days and produce a list of partition names and the max(event_date) for each partition. Assume a metadata table partitions(name TEXT, range_start DATE, range_end DATE). Provide SQL and recommend a safe procedure to drop old partitions.

EasyTechnical

0 practiced

As a Data Engineer working with PostgreSQL, explain what a Common Table Expression (CTE) is, show the basic WITH syntax and give a minimal example that computes the average order amount per user. Example SQL: WITH avg_per_user AS (SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id) SELECT user_id, avg_amt FROM avg_per_user WHERE avg_amt > 100; Explain where CTEs sit in logical execution order and why they help readability and maintainability.

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.