SQL · easy

Write a query to find the second highest salary from an Employees table.

Asked in Data Analyst interviews, in the SQL round.

Short answer

Use a subquery with MAX where salary < (SELECT MAX(salary)), or DENSE_RANK() = 2.

How to answer it

Two answers are expected; the second is the one that shows you know window functions.

The subquery version reads naturally: the largest salary that is smaller than the largest salary.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The window version generalises to "Nth highest" and handles ties the way the interviewer probably wants:

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

DENSE_RANK, not RANK or ROW_NUMBER: if two people share the top salary, ROW_NUMBER would call one of them "second", and RANK would skip 2 entirely and return nothing. DENSE_RANK returns the second distinct value, which is what "second highest" means. If there is only one distinct salary, both queries return NULL or no row; say so before they ask.

• What they are checking: whether you reach for a window function, and whether you know what happens on ties.
• Common mistake: ORDER BY salary DESC LIMIT 1 OFFSET 1. It works on unique salaries and is wrong the moment two people earn the same amount.

Related questions

• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.
• Find users who logged in 3 or more consecutive days.

Practice this for real

• Check your resume against a Data Analyst job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card