Subqueries and CTEs Interview Questions for SQL

Show answer

A subquery is a query nested inside another statement. It can appear in WHERE, such as salary > (SELECT AVG(salary) FROM employees), in FROM as a derived table, in SELECT as a scalar value, and in HAVING. It can return a single value, a list for IN, or a table. Subqueries break complex logic into smaller steps.

What is the difference between a correlated and a non-correlated subquery?

Show answer

A non-correlated subquery runs independently and returns a result once, such as the overall average salary. A correlated subquery references columns from the outer query and logically runs for each outer row, such as comparing each employee's salary with the average of their own department. Correlated subqueries are powerful but may be slow without good indexes, although optimisers often rewrite them as joins.

What is a CTE and why use it?

Show answer

A Common Table Expression is a named temporary result defined with WITH before the main query. It improves readability by breaking a complex query into named steps, can be referenced multiple times in the same statement, and supports recursion. Example: WITH high_value AS (SELECT * FROM orders WHERE amount > 10000) SELECT customer_id, COUNT(*) FROM high_value GROUP BY customer_id.

Find employees earning more than the company average.

Show answer

SELECT emp_id, name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) ORDER BY salary DESC; The inner query is a non-correlated scalar subquery that runs once and returns one value used for comparison.

Find the second highest salary.

Show answer

SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); With a window function: SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; Both handle duplicate top salaries correctly.

How do you approach a SQL problem you have never seen before in an interview?

Show answer

Situation: in a campus interview I got a problem about consecutive login days that I had not practised. Task: solve it while explaining my thinking. Action: I clarified the table and expected output, wrote a small example, broke it into steps with CTEs, and talked through each step before writing the final query. Result: I solved it with a small hint and the interviewer appreciated the structured approach.

What is the difference between IN and EXISTS?

Show answer

IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.

Why is NOT IN dangerous when the subquery contains NULL?

Show answer

If the subquery returns any NULL, value NOT IN (list) becomes unknown for every row, because comparing with NULL is unknown, so the query returns no rows. For example, NOT IN (SELECT manager_id FROM employees) fails when a manager_id is NULL. Use NOT EXISTS or filter out NULLs in the subquery to get correct results.

What is the difference between a CTE, a subquery, a temp table and a view?

Show answer

A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.

Are CTEs materialised or inlined?

Show answer

It depends on the database. SQL Server and MySQL usually inline CTEs like subqueries. PostgreSQL before version 12 always materialised CTEs, which could hurt performance; since version 12 non-recursive CTEs referenced once are inlined, and you can force behaviour with MATERIALIZED or NOT MATERIALIZED. Referencing a heavy CTE many times may cause repeated work, so check the plan.

Find employees who earn more than their department average.

Show answer

SELECT e.emp_id, e.name, e.department_id, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id ); This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.

Rewrite a nested query using CTEs for readability.

Show answer

WITH order_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), ranked AS ( SELECT customer_id, total, NTILE(10) OVER (ORDER BY total DESC) AS decile FROM order_totals ) SELECT * FROM ranked WHERE decile = 1; Each CTE is a named step, making top 10 percent customer logic easy to read.

Find departments where every employee earns above 40000.

Show answer

SELECT d.department_id, d.name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary <= 40000 ) AND EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id ); Double negation expresses for all; the EXISTS excludes empty departments.

Your NOT IN query returns zero rows even though you expect results. What do you check?

Show answer

Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.

A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?

Show answer

Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.

You need to delete duplicate rows but keep one copy of each. How do you do it?

Show answer

Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.

An interviewer asks for the Nth highest salary in a reusable way. How do you answer?

Show answer

Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.

Describe a time you reviewed someone else's SQL and suggested improvements.

Show answer

Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.

What is a recursive CTE and how does it work?

Show answer

A recursive CTE has an anchor member that returns the starting rows and a recursive member that references the CTE itself, joined with UNION ALL. The database repeats the recursive part using the previous iteration's rows until no new rows appear. It is used for hierarchies like org charts, bill of materials and generating sequences. Add a depth limit to prevent infinite loops on cyclic data.

What is a lateral join or CROSS APPLY?

Show answer

A lateral join, written as JOIN LATERAL in PostgreSQL and MySQL 8 or CROSS APPLY and OUTER APPLY in SQL Server, lets a subquery in FROM reference columns of tables listed before it. It acts like a correlated subquery that can return multiple rows and columns, ideal for top N per group, for example the three latest orders for each customer.

Write a recursive CTE to show an employee hierarchy with levels.

Show answer

WITH RECURSIVE org AS ( SELECT emp_id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id ) SELECT * FROM org ORDER BY lvl, name; SQL Server omits the RECURSIVE keyword.

Get the latest 3 orders for every customer using a lateral join.

Show answer

SELECT c.customer_id, o.order_id, o.order_date FROM customers c CROSS JOIN LATERAL ( SELECT order_id, order_date FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3 ) o; With an index on (customer_id, order_date), each lookup is fast. SQL Server uses CROSS APPLY with TOP 3.

A long report query has eight nested subqueries and nobody can maintain it. How would you refactor it?

Show answer

Break it into CTEs with meaningful names, each producing one business step such as active customers, order totals and returns. Remove repeated logic, standardise filters, and add comments on business rules. Test each step's row count against the original output. If parts are reused across reports, move them into views. Finally compare results and execution plans to ensure nothing changed.

A recursive CTE on a category tree runs forever. What is the likely cause and fix?

Show answer

The data probably has a cycle, such as category A as parent of B and B as parent of A, so recursion never ends. Add a depth column and stop at a maximum level, track a path string or array and exclude nodes already visited, and use database limits such as MAXRECURSION in SQL Server. Then fix the cyclic data at the source with constraints or validation.

Tell me about a time you set SQL coding standards for a team.