✏️ Explanatory Question
Level: Coding Round — A LeetCode classic; the edge case (asking for the 5th highest when only 3 exist) is the real test.
The Puzzle: Write a query to return the Nth highest DISTINCT salary from an employees table. If there is no Nth highest salary (e.g., you ask for the 4th but only 3 distinct salaries exist), the query must return NULL, not an empty result.
employees| id | salary |
|---|---|
| 1 | 100000 |
| 2 | 85000 |
| 3 | 85000 |
| 4 | 70000 |
| SecondHighestSalary |
|---|
| 85000 |
Distinct salaries are 100k, 85k, 70k. The 2nd highest is 85k. Asking for N=4 would return NULL (only 3 distinct values exist).
Two things to get right: (1) Use DISTINCT so tied salaries count as one level, and (2) wrap the result so that no matching row still returns NULL rather than zero rows. A bare LIMIT ... OFFSET returns an empty set when N is too large — the edge case most candidates miss.
-- The outer SELECT guarantees a NULL row when the inner query is empty
SELECT (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1 -- OFFSET = N - 1 (skip the top N-1)
) AS SecondHighestSalary;
-- If no 2nd salary exists, the scalar subquery yields NULL The OFFSET rule: To get the Nth highest, use LIMIT 1 OFFSET N-1 — you skip the top N-1 rows and take the next one. For the 2nd highest, that is OFFSET 1. Wrapping it in an outer SELECT (...) converts "no rows" into a single NULL row, satisfying the requirement.
DELIMITER $$
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE offset_val INT;
SET offset_val = N - 1; -- convert N to a 0-based offset
RETURN (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET offset_val
); -- returns NULL automatically if the offset is out of range
END $$
DELIMITER ;
-- Call it for any N
SELECT getNthHighestSalary(2) AS nth_salary; -- 85000
SELECT getNthHighestSalary(4) AS nth_salary; -- NULL-- MAX() over an empty match returns NULL, so the edge case is handled
SELECT MAX(salary) AS NthHighestSalary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees
) t
WHERE drnk = 4; -- set to any N; returns NULL if no such rank exists Why Solution 3 is elegant: When no row has drnk = N, the WHERE yields no rows, but MAX() over an empty set returns NULL — the edge case is handled for free, without an extra wrapping subquery. DENSE_RANK also correctly treats tied salaries as one rank.
Interviewer follow-up: "Do this per department — the Nth highest salary in EACH department." → Add PARTITION BY dept_id to the DENSE_RANK, then filter WHERE drnk = N and GROUP BY dept_id. Departments with fewer than N distinct salaries simply return no row (or NULL if you LEFT JOIN back to all departments) — the same edge case, now per group.