Advanced SQL Interview Questions for Experienced Professionals

Q1: How does index selectivity change the query plan?

The question: "You have an index on orders.status. Why might the optimizer ignore it for WHERE status = 'shipped' but use it for WHERE status = 'refunded'?"

Answer: Selectivity — how large a fraction of the table a condition matches. If 70% of orders are 'shipped', using the index means jumping between the index and the table for millions of rows, which ends up slower than just scanning the whole table sequentially. If only 0.1% of orders are 'refunded', the index lets the database skip straight to that tiny fraction — a clear win. The optimizer estimates this from stored table statistics, not by guessing; a strong answer mentions that stale statistics (after a large bulk load with no ANALYZE/UPDATE STATISTICS run) can cause the optimizer to make the wrong call for either case.

Q2: Calculate a median without PERCENTILE_CONT

The question: "Find the median salary in a table, without relying on a built-in percentile function."

WITH ordered AS (
  SELECT salary,
    ROW_NUMBER() OVER (ORDER BY salary) AS rn,
    COUNT(*) OVER ()               AS total_rows
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN (
  FLOOR((total_rows + 1) / 2.0),
  CEIL((total_rows + 1) / 2.0)
);

For an odd row count, FLOOR and CEIL land on the same middle row, and AVG of one value is just that value. For an even count, they land on the two middle rows, and AVG correctly averages them — one query handles both cases without branching logic.

Q3: What is a phantom read, and which isolation level stops it?

The question: "Explain a phantom read with a concrete example, and name the isolation level that prevents it."

Transaction A re-ran the exact same query and got a different row count — a new "phantom" row appeared that wasn't there the first time, within the same still-open transaction. SERIALIZABLE is the isolation level that prevents this, by ensuring the transaction behaves as if no other transaction ran concurrently at all. REPEATABLE READ prevents a different, narrower problem — existing rows changing value mid-transaction — but does not guarantee protection against new rows appearing in every database. Full breakdown of all four levels in SQL Isolation Levels Explained.

Q4: Rewrite a correlated subquery for performance

The question: "This query is correct but slow on a large table. Rewrite it."

SELECT o.*
FROM orders o
WHERE o.amount > (
  SELECT AVG(o2.amount)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

SELECT *
FROM (
  SELECT o.*,
    AVG(amount) OVER (
      PARTITION BY customer_id
    ) AS customer_avg
  FROM orders o
) t
WHERE amount > customer_avg;

Why the rewrite matters: the correlated version conceptually recomputes the per-customer average once per outer row (though a good optimizer may partially mitigate this). The window-function version computes every customer's average in a single pass over the table, then filters — one scan instead of a scan-per-row pattern. Whether the database's real optimizer actually executes the original as badly as it "conceptually" reads is implementation-dependent, which is exactly why checking EXPLAIN on both versions, rather than assuming, is the correct senior-level instinct.

Q5: Protect a recursive CTE from cycles

The question: "Your recursive CTE walks a graph, and the data might contain a cycle (A reports to B, B reports to A). How do you prevent an infinite loop?"

WITH RECURSIVE path_search AS (
  SELECT employee_id, manager_id,
    ARRAY[employee_id] AS visited,
    FALSE AS is_cycle
  FROM employees
  WHERE employee_id = 1

  UNION ALL

  SELECT e.employee_id, e.manager_id,
    ps.visited || e.employee_id,
    e.employee_id = ANY(ps.visited)   -- true if we've seen this node before
  FROM employees e
  JOIN path_search ps ON e.manager_id = ps.employee_id
  WHERE NOT ps.is_cycle                -- stop extending a path that already cycled
)
SELECT * FROM path_search;

Beyond the depth-cap technique from basic recursive CTE questions, this tracks the full visited path as an array and checks whether the next node has already appeared in it — the standard graph-traversal cycle-detection technique, adapted to SQL. A senior answer also mentions the database's built-in recursion-depth safety net (SQL Server's MAXRECURSION, for instance) as a second line of defense, not a replacement for explicit cycle detection.

Q6: Clustered index vs. covering index

The question: "What's the difference between a clustered index and a covering index, and can an index be both?"

These describe different things and aren't mutually exclusive — a clustered index can also happen to cover a particular query if it includes every column that query needs, but "covering" describes a relationship between one index and one specific query, not a property of the index by itself.