Cracking the Nth Highest Salary: A Key Amazon SQL Interview Question

As a Business Intelligence Engineer at Amazon, I've seen my fair share of SQL interview questions. One that often pops up is the "Nth Highest Salary" problem.

Whether you're prepping for an interview or just looking to sharpen your SQL skills, tackling this problem is a great exercise. It touches on several key SQL concepts like subqueries, sorting, and limiting results.

So, let's dive in and explore how to crack this common SQL interview question!

Problem Statement

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+

| Id | Salary |

+----+--------+

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

+------------------------+

| getNthHighestSalary(2) |

+------------------------+

| 200 |

+------------------------+

Solution

We'll use a subquery with LIMIT and OFFSET to achieve this.

The idea is to order the salaries in descending order, skip N-1 salaries, and then take the next one. If there's no such salary, we should return null.

However, since SQL functions can't directly return null in the way we might expect when using LIMIT and OFFSET (especially if the offset exceeds the number of rows), we need to ensure our query is set up to handle this gracefully.

Here's a way to structure the query to ensure it behaves correctly:

DELIMITER $$

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
    SELECT DISTINCT Salary FROM (
      SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N, 1
    ) AS Temp
  );
END$$

DELIMITER ;

Explanation:

📌 Setting N: We adjust N by subtracting 1 because OFFSET starts at 0. So, for the 2nd highest salary, we actually want to offset by 1.

📌 Subquery with LIMIT and OFFSET: The subquery SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N, 1 selects salaries in descending order, skips the first N-1 salaries (because we adjusted N), and then limits the result to 1 row. This effectively gets us the nth highest salary.

📌 Handling NULL: If there's no nth highest salary (for example, if we ask for the 10th highest salary but there are only 5 employees), the subquery will return an empty set. The outer query (SELECT DISTINCT Salary FROM (...) AS Temp) ensures that we return NULL in such cases, as there's no row to select from.

📌 Creating a Function: We wrap this logic in a SQL function named getNthHighestSalary that takes an integer N as its parameter. This makes it easy to reuse this logic to find the nth highest salary by simply calling this function with the desired N.

📌 DELIMITER: The DELIMITER command is used to change the statement delimiter for the MySQL client. This is necessary because our function contains semicolons, and we want to prevent the client from interpreting them as the end of the entire function definition. After defining the function, we reset the delimiter back to ;.

*****************

To wrap things up, I hope this walkthrough of the "Nth Highest Salary" problem has been helpful.

SQL questions like this are great for honing your data manipulation skills, which are important in roles like mine at Amazon and across the tech industry.

If you're aiming for a position at Amazon or just want to level up your SQL skills, there are plenty more challenges like this to tackle.

Feel free to reach out if you'd like to discuss more SQL problems or if you have questions about working in a data role at Amazon.

I'm always happy to connect with fellow data enthusiasts and help you with your interview preparation.