Second Highest Salary - Practice Coding Problems

Second Highest Salary - Problem

Database Medium

🏢 Find the Second Highest Salary

You're working as a data analyst at a tech company and need to find the second highest distinct salary from the employee database. This is a common task in HR analytics!

Given an Employee table with the following structure:

Column Name	Type
id	int
salary	int

Key Points:

id is the primary key (unique values)
Each row contains salary information for one employee
Multiple employees can have the same salary
If there's no second highest salary, return null

Your task: Write a SQL query to find the second highest distinct salary. Remember, we want the second unique salary value, not the second occurrence!

Input & Output

example_1.sql — Basic Case

$ Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

› Output: SecondHighestSalary: 200

💡 Note: The distinct salaries are [300, 200, 100]. The second highest distinct salary is 200.

example_2.sql — Duplicate Salaries

$ Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 200 | | 4 | 100 | +----+--------+

› Output: SecondHighestSalary: 100

💡 Note: The distinct salaries are [200, 100]. Even though 200 appears twice and 100 appears twice, we only consider unique values. The second highest distinct salary is 100.

example_3.sql — No Second Highest

$ Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | +----+--------+

› Output: SecondHighestSalary: null

💡 Note: All employees have the same salary (100), so there is only one distinct salary. Since there's no second highest distinct salary, we return null.

Visualization

Tap to expand

Understanding the Visualization

Collect Employee Data

Gather all employee salaries from the database, including potential duplicates

Identify Unique Salary Levels

Find distinct salary amounts to understand the pay structure

Rank Salary Tiers

Sort salaries from highest to lowest to create a ranking system

Extract Second Tier

Select the second-highest distinct salary for analysis

Key Takeaway

🎯 Key Insight: The secret to solving this problem correctly is understanding that we want the second distinct salary value, not the second occurrence. DENSE_RANK() window function is perfect for this because it assigns consecutive ranks without gaps, even when there are ties!

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for ranking, but done efficiently by the database engine

⚡ Linearithmic

Space Complexity

O(n)

Space for ranking intermediate results

⚡ Linearithmic Space

Constraints

The Employee table contains at least 0 rows
Salary values are integers and can be negative
id is the primary key with unique values
Important: Return exactly null (not 0 or empty) when no second highest exists

Asked in

G Google 127 a Amazon 89 ⊞ Microsoft 76 f Meta 54

The optimal solution uses DENSE_RANK() window function to rank distinct salaries in descending order and selects rows with rank = 2. This approach handles duplicate salaries correctly and returns null when no second highest exists. Alternative approaches include subqueries with MAX or DISTINCT with LIMIT/OFFSET, but window functions provide the cleanest and most efficient solution for modern SQL databases.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery with MAX and NOT IN	O(n²)	O(1)	Find max salary, then find max of remaining salaries
Window Functions (ROW_NUMBER/DENSE_RANK)	O(n log n)	O(n)	Use DENSE_RANK to rank distinct salaries and select rank 2
DISTINCT with LIMIT and OFFSET	O(n log n)	O(n)	Get distinct salaries, order them, and use LIMIT with OFFSET

Subquery with MAX and NOT IN — Algorithm Steps

Find the maximum salary from all employees
Find the maximum salary that is less than the overall maximum
Handle the case where no second highest exists (return null)

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Maximum

Inner query finds the highest salary

Filter and Find Max

Outer query finds max of remaining salaries

Code -

solution.c — C

// SQL query using subquery approach
// SELECT MAX(salary) AS SecondHighestSalary  
// FROM Employee
// WHERE salary < (SELECT MAX(salary) FROM Employee)

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

int compare(const void *a, const void *b) {
    return (*(int*)b - *(int*)a); // Descending order
}

int findSecondHighest(int* salaries, int n) {
    if (n < 2) return -1; // Use -1 to indicate null
    
    // Sort in descending order
    qsort(salaries, n, sizeof(int), compare);
    
    // Find second distinct value
    for (int i = 1; i < n; i++) {
        if (salaries[i] != salaries[0]) {
            return salaries[i];
        }
    }
    
    return -1; // No second highest found
}

int main() {
    char input[1000];
    fgets(input, sizeof(input), stdin);
    
    int salaries[100];
    int count = 0;
    
    char* token = strtok(input, " \n");
    while (token != NULL && count < 100) {
        salaries[count++] = atoi(token);
        token = strtok(NULL, " \n");
    }
    
    int result = findSecondHighest(salaries, count);
    if (result == -1) {
        printf("null\n");
    } else {
        printf("%d\n", result);
    }
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Two separate scans through the table to find max values

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond the query execution

✓ Linear Space

Constraints

The Employee table contains at least 0 rows
Salary values are integers and can be negative
id is the primary key with unique values
Important: Return exactly null (not 0 or empty) when no second highest exists

73.6K 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

🏢 Find the Second Highest Salary

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Subquery with MAX and NOT IN — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler