Amazon SQL interview questions – Leetcode 177 – Nth Highest Salary.

Problem Description –

Write a SQL query to get the n^th highest salary from the Employee table.

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

Difficulty Level – Medium

Problem Link – https://leetcode.com/problems/nth-highest-salary/

Solution –

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
        salary as getNthHighestSalary
      FROM (
      SELECT
        salary,
        DENSE_RANK() OVER(ORDER BY salary DESC) as sal_rank
      FROM Employee
          ) t1
      WHERE sal_rank = N
      LIMIT 1  
      
  );
END

To rank all the salaries of the employees, we are using the DENSE_RANK() function and ordering the salary in descending order, so that the highest salary will get the rank of 1. Then in the outer query, we are using the WHERE statement to select the Nth highest salary and also LIMIT the result to 1 to show only one value. You can also use DISTINCT instead of the limit.

And the reason for not using the RANK() function is that if we have duplicate rows, then the RANK() function will leave holes in the ranking, which will give you the wrong answer.

SELECT salary,
        RANK() OVER(ORDER BY salary DESC) as sal_rank
 FROM Employee

Here, you can see that as we have two salaries of 300, both get the rank of 1 but the second-highest salary gets the rank of 3 as the RANK() function leaves holes in the ranking when we have duplicate rows.

But if we use the DENSE_RANK() function, we will get the right result.

SELECT salary,
        DENSE_RANK() OVER(ORDER BY salary DESC) as sal_rank
 FROM Employee

Here, you can see that the salary of 200 gets the ranking of 2, which is the correct response. When to use which function depends on the problem. Here, using DENSE_RANK() will give you the correct result.

There is also a ROW_NUMBER() function, which will give a unique rank to each row whether you have duplicate data or not.

SELECT salary,
        ROW_NUMBER() OVER(ORDER BY salary DESC) as sal_rank
 FROM Employee