Second Highest Salary Interview Questions

I would first ask: second-highest distinct salary, or the second row after sorting, which can still be the same salary if two people tie for first. The classic query is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). That gives the distinct second-highest. The version I prefer now is DENSE_RANK() OVER (ORDER BY salary DESC) and then filter rank = 2. DENSE_RANK does not skip 2 when two people share the max. RANK() would skip. LIMIT 1 OFFSET 1 is dialect-specific and wrong under ties unless I DISTINCT the salaries first. I would write DENSE_RANK unless they are on an old MySQL without window functions.

One: ORDER BY salary DESC OFFSET 1 LIMIT 1, with DISTINCT if they want unique salary values. Two: a subquery WHERE salary is less than MAX(salary), then MAX of that. Three: DENSE_RANK in a CTE and filter rank = 2. I would ask whether two people with the same top salary means second is that same number or the next lower. DISTINCT versus DENSE_RANK versus RANK changes the answer. I say that before I write SQL.

I would use DENSE_RANK() OVER (ORDER BY salary DESC) and filter n. OFFSET n-1 is fine for distinct sorted salaries if the dialect supports it. I would not stack N-1 nested MAX subqueries. I mention NULL salaries: I usually filter them out before ranking. If they want per department, I PARTITION BY department_id. That is the follow-up inside the follow-up.

I would return NULL, not an empty error, unless they want no row. OFFSET can return empty. MAX of an empty set is NULL. In interviews I say I would wrap it so the API gets null. I would not fake a zero. Two employees with the same salary: I confirm whether that counts as one level. These edge cases are the difference between a query that demos well and a query that is wrong on real data.

On a large table both need a good plan. ORDER BY salary DESC LIMIT 2 can use an index on salary. A subquery with MAX might scan twice. I would look at EXPLAIN. I would not fight religion. I pick the one that uses the index in this engine. For a homework-size table it does not matter. For production I show I would check the plan.