Intermediate SQL Interview Questions for Software Engineer

Intermediate-level SQL interview questions for Software Engineer positions.

1. Write a query to find the Nth highest salary in a company.

Answer: Multiple approaches: 1) Subquery with COUNT, 2) OFFSET/LIMIT, 3) Window function with DENSE_RANK. Window function is most flexible for handling ties.

-- Method 1: Using OFFSET (N=3 for 3rd highest)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 2;

-- Method 2: Using subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees); -- 2nd highest

-- Method 3: Using window function (handles ties)
WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rank = 3;