Forum

Forums
Power BI
Q&A
Deloitte: Write an ...
 
Share:
Notifications
Clear all

Deloitte: Write an SQL Query to Find the Nth Highest Salary Without Using LIMIT, TOP, or OFFSET

 
Q&A
Last Post by Anonymous 4 months ago
1 Posts
1 Users
0 Reactions
113 Views
RSS
Posts: 5
 Kalyan
Topic starter
Nov 18, 2025 3:11 am
(@Kalyan)
Joined: 4 months ago

Deloitte: Write an SQL Query to Find the Nth Highest Salary Without Using LIMIT, TOP, or OFFSET

This is a very popular question asked in Deloitte SQL and Data Engineer interviews. It tests a candidate’s ability to use ranking functions, subqueries, and advanced SQL logic to solve problems without relying on simple shortcuts.

✅ Approach 1: Using DENSE_RANK()

You can use a window function to assign ranks to salaries and then filter by the required N.


SELECT salary
FROM (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked_salaries
WHERE rnk = :N;

Replace :N with the value of the Nth highest salary you want. For example, to get the 3rd highest salary:


WHERE rnk = 3;

✅ Approach 2: Using a Subquery

This method returns the salary for the Nth highest value using a distinct subquery filter.


SELECT DISTINCT salary
FROM employees e1
WHERE :N - 1 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);

📌 Explanation in Simple Words

“To find the Nth highest salary, you cannot rely on LIMIT, TOP, or OFFSET in many interviews. Instead, I use a window function such as DENSE_RANK() to rank salaries in descending order and select the row matching the required rank. Alternatively, I can use a correlated subquery that counts how many unique salaries are greater than the current one. When this count equals N−1, that salary is the Nth highest.”

💬 Why Deloitte Asks This

Deloitte expects candidates to be comfortable with window functions, correlated subqueries, filtering logic, and ranking mechanisms. These skills are essential for solving real-world data problems involving complex transformations and analytical queries.


Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: