Write a query to find the second highest salary from the Employee table. If there is no second highest salary, return NULL.
This classic interview question tests your understanding of aggregate functions and subqueries. Airbnb frequently uses this to assess candidates' ability to handle ranking and filtering operations, which are common in their pricing and availability systems. The challenge lies in handling edge cases: what if there's only one unique salary? What if all salaries are the same? Your solution must gracefully return NULL in these scenarios.
This problem primarily tests: MAX() aggregate function, nested subqueries for comparison, handling NULL values when no second highest exists, and understanding DISTINCT for duplicate handling. Alternative approaches include ORDER BY with LIMIT/OFFSET or window functions like DENSE_RANK().
In production systems, similar queries are used for: finding the second-best pricing option for dynamic pricing algorithms, identifying runner-up candidates in recommendation systems, analyzing performance metrics where you need to exclude the top outlier, and calculating fallback values in availability searches when the primary option is unavailable.
When tackling this Airbnb problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);This approach uses a correlated scalar subquery to find the global maximum salary, then applies MAX() to the remaining values that fall strictly below it. It returns NULL naturally when fewer than two distinct salaries exist — exactly the expected output. The logic is easy to follow and performs well on indexed salary columns. However, it only works for the second highest and does not generalize to the Nth highest without nesting additional subqueries, making it less flexible for follow-up variations.
SELECT salary AS SecondHighestSalary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;DENSE_RANK() assigns the same rank to duplicate salaries and leaves no gaps, so truly the second distinct value receives rank 2. Wrapping the result in a subquery and filtering on rnk = 2 cleanly handles ties and is trivially extensible to any rank N — just change the filter. The LIMIT 1 guards against multiple employees sharing that salary. The trade-off is slightly higher overhead on very large tables without a covering index on salary, but the window function approach is generally preferred in modern SQL environments for its readability and flexibility.
For tables with millions of rows, both approaches benefit greatly from a B-tree index on the salary column. The correlated subquery scans the table twice — once for the inner MAX and once for the outer MAX — so without an index it performs two full scans: O(2n). A composite index on (salary) allows both passes to use index-only scans, making them extremely fast. The DENSE_RANK() window function approach typically performs a single sort pass over the entire dataset, which can be more expensive in memory for large tables but is executed in a single scan. Modern optimizers (PostgreSQL, MySQL 8+, SQL Server) often push the LIMIT clause down to avoid materializing the full ranked result. At scale, if this query runs frequently, a pre-computed or cached value in a summary table may be more appropriate. Always benchmark with EXPLAIN / EXPLAIN ANALYZE to confirm index usage in your specific database engine.
The most common mistake is using MIN() instead of MAX() in the subquery, which returns a completely wrong result. Another frequent error is not accounting for NULL salary values — both approaches silently ignore NULLs (since MAX() skips them), which is usually correct, but candidates should explicitly state this assumption. Using ORDER BY salary DESC LIMIT 1 OFFSET 1 is a popular shortcut but it returns the second row, not the second distinct salary — if two employees share the top salary, OFFSET 1 incorrectly returns that same salary value again. Candidates also sometimes forget the NULL return requirement when only one distinct salary exists, which the subquery approach satisfies automatically but an OFFSET approach does not.
The cleanest generalization uses DENSE_RANK(): wrap the table in a subquery that computes DENSE_RANK() OVER (ORDER BY salary DESC), then filter WHERE rnk = N. For the subquery approach you would need N-1 nested NOT IN / less-than subqueries, which becomes unwieldy. A CTE with DENSE_RANK is preferred because changing a single integer handles any N without restructuring the query.
ROW_NUMBER assigns a unique sequential number regardless of ties, so two employees with the same salary get different row numbers — unreliable for 'second highest distinct salary'. RANK skips numbers after ties (1,1,3), so filtering on rank = 2 could return nothing if two employees share first place. DENSE_RANK never skips (1,1,2), making it the correct choice when you want the second distinct salary value.
With the subquery approach, the WHERE clause (salary < MAX(salary)) filters out every row, so the outer MAX() has no input and returns NULL — the correct result since there is no second highest salary. The DENSE_RANK approach similarly returns no rows with rnk = 2, and the LIMIT 1 without a match returns an empty set, not NULL. To ensure NULL is returned in that case, wrap the window-function version in an outer SELECT with IFNULL or COALESCE.