Nth Highest Salary - Practice Coding Problems

Nth Highest Salary - Problem

Database Medium

You're working with a company's Employee database that contains salary information. Your task is to find the n^th highest distinct salary from the Employee table.

The Employee table structure:

Column Name	Type
id	int
salary	int

Important points:

id is the primary key with unique values
You need to find distinct salaries (duplicates don't count)
If there are fewer than n distinct salaries, return null
The ranking is from highest to lowest (1st highest, 2nd highest, etc.)

Example: If salaries are [100, 200, 300, 200], the distinct salaries in order are [300, 200, 100]. So the 2nd highest distinct salary is 200.

Input & Output

example_1.sql — Basic Case

$ Input: Employee table: [{"id": 1, "salary": 100}, {"id": 2, "salary": 200}, {"id": 3, "salary": 300}], n = 2

› Output: 200

💡 Note: The distinct salaries in descending order are [300, 200, 100]. The 2nd highest distinct salary is 200.

example_2.sql — With Duplicates

$ Input: Employee table: [{"id": 1, "salary": 100}, {"id": 2, "salary": 200}, {"id": 3, "salary": 300}, {"id": 4, "salary": 200}], n = 2

› Output: 200

💡 Note: The distinct salaries are [300, 200, 100]. Even though 200 appears twice, it's still the 2nd highest distinct salary.

example_3.sql — Not Enough Salaries

$ Input: Employee table: [{"id": 1, "salary": 100}], n = 2

› Output: null

💡 Note: There is only 1 distinct salary (100), but we need the 2nd highest. Since there aren't enough distinct salaries, we return null.

Visualization

Tap to expand

Understanding the Visualization

Gather All Scores

Collect all employee salaries from the database table

Apply DENSE_RANK()

Assign consecutive ranks to distinct salary values, handling ties appropriately

Filter by Position

Find all salaries that have exactly the nth rank

Return Result

Return the salary value or null if the position doesn't exist

Key Takeaway

🎯 Key Insight: DENSE_RANK() is the perfect tool for finding nth distinct highest values because it automatically handles duplicates while maintaining consecutive ranking, exactly matching our requirement for distinct salary positions.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database-optimized window function, typically faster than manual sorting

⚡ Linearithmic

Space Complexity

O(n)

Space for ranking calculation, handled efficiently by database

⚡ Linearithmic Space

Constraints

The Employee table contains at least 0 rows
1 ≤ n ≤ 500
salary values can be any integer
Return null if there are fewer than n distinct salaries
id values are unique (primary key constraint)

Asked in

G Google 85 a Amazon 72 ⊞ Microsoft 68 f Meta 45 Apple 35 O Oracle 92

The optimal solution uses the DENSE_RANK() window function to efficiently rank salaries in descending order. This approach automatically handles duplicate salaries by assigning them the same rank while maintaining consecutive ranking for distinct values. The query filters results where the rank equals n, returning the nth highest distinct salary or null if insufficient distinct salaries exist. Time complexity is O(n log n) with database optimizations.

Common Approaches

Approach	Time	Space	Notes
✓ DISTINCT + ORDER BY + LIMIT	O(n log n)	O(n)	Remove duplicates, sort salaries, then pick the nth one
Brute Force (Nested Subqueries)	O(n²)	O(1)	Count distinct salaries greater than each salary to find ranking
DENSE_RANK() Window Function (Optimal)	O(n log n)	O(n)	Use DENSE_RANK() to efficiently handle duplicates and get exact ranking

DISTINCT + ORDER BY + LIMIT — Algorithm Steps

Select all distinct salaries from the Employee table
Sort the distinct salaries in descending order
Use LIMIT with OFFSET to get the nth highest salary
Handle case where n exceeds the number of distinct salaries

Visualization

Tap to expand

Step-by-Step Walkthrough

Get DISTINCT Salaries

Remove duplicate salary values

ORDER BY DESC

Sort salaries from highest to lowest

LIMIT with OFFSET

Skip n-1 rows and take the next one

Return Result

Get the nth highest distinct salary

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>

void getNthHighestSalarySimple(int n) {
    printf("SELECT DISTINCT salary "
           "FROM Employee "
           "ORDER BY salary DESC "
           "LIMIT 1 OFFSET %d", n-1);
}

int main() {
    int n;
    scanf("%d", &n);
    getNthHighestSalarySimple(n);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting distinct salaries dominates the time complexity

⚡ Linearithmic

Space Complexity

O(n)

Space needed to store distinct salaries and sorting

⚡ Linearithmic Space

Constraints

The Employee table contains at least 0 rows
1 ≤ n ≤ 500
salary values can be any integer
Return null if there are fewer than n distinct salaries
id values are unique (primary key constraint)

89.4K Views

Very High Frequency

~15 min Avg. Time

2.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

DISTINCT + ORDER BY + LIMIT — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler