Write a SQL query to find the second highest salary
Reported in Roblox USA engineering loops. Classic SQL screening question testing subqueries, window functions, or ordering.
Interview scenario
Context for Roblox candidates:
Assume a table employees(id, name, salary) with possible duplicate salaries.
Model answer
Try answering aloud first
Cover trade-offs, structure, and a concrete example before revealing the baseline response.
How to frame this at Roblox: Connect your answer to measurable impact, clarity of thought, and trade-offs the team cares about. Below is a strong baseline response you can adapt with your own project examples.
Subquery approach (works on all SQL dialects):
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);Window function (cleaner when duplicates exist):
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;DENSE_RANK handles ties—two employees at the top salary still leave a well-defined second rank. ROW_NUMBER would pick arbitrarily among ties.
Edge cases to mention: return NULL or empty set when fewer than two distinct salaries exist; discuss indexing on salary for large tables.
Discussion
Comments (0)
Share how this question came up in your loop, or add tips for others preparing.
Log in to comment on this question.