Question:
Write an SQL query to find the nth highest salary in the Employees table. The nth highest salary is the salary that appears in the nth position when all the salaries are sorted in descending order. The value of n should be specified as a parameter in the query. Ensure that your solution handles cases where there are duplicate salaries and returns the correct result for any valid value of n.

Table Schema:

employee_id (int): A unique identifier for each employee.
first_name (varchar): The first name of the employee.
last_name (varchar): The last name of the employee.
salary (decimal): The salary of the employee.

Schema

The Employees table might have the following schema:

CREATE OR REPLACE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

Dataset

Let's populate the Employees table with some sample data to illustrate the query:

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES
    (1, 'John', 'Doe', 60000.00),
    (2, 'Jane', 'Smith', 75000.00),
    (3, 'Emily', 'Davis', 80000.00),
    (4, 'Michael', 'Brown', 60000.00),
    (5, 'Sarah', 'Johnson', 90000.00);

Solution

WITH salary_ranks AS (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT salary
FROM salary_ranks
WHERE rank = :n;  -- Replace :n with the desired value of n

Explanation

DENSE_RANK(): This function assigns a rank to each salary based on the order of the salaries in descending order. If two employees have the same salary, they will get the same rank, and the next distinct salary will get the next consecutive rank (not skipping any ranks).
WITH SalaryRanks AS (...): The Common Table Expression (CTE) calculates the rank for each salary in descending order. The result is stored in a temporary "SalaryRanks" table.
WHERE rank = :n: This part of the query filters the salaries that correspond to the nth highest rank, where :n is a placeholder for the parameter value. You need to replace :n with the actual value of n when executing the query.

Github:

https://github.com/vipinputhanveetil/sql-interview-questions-series/blob/main/sql_interview_question_2.sql

Handling Duplicates:

If there are duplicate salaries, they will receive the same rank, so if you request, for example, the 3rd highest salary and there are duplicates, the function will still consider the appropriate rank and return the correct salary.

Edge Cases:

If n exceeds the number of distinct salaries in the table, the query will return no rows. You might want to handle this case in your application logic by checking if the result is empty.

Conclusion:

If n exceeds the number of distinct salaries in the table, the query will return no rows. You might want to handle this case in your application logic by checking if the result is empty.

Stay tuned for more articles in this SQL Interview Questions Series as we delve deeper into a variety of questions and scenarios asked by top companies!

SQL Interview Questions Series#2