Second Highest Salary II - Practice Coding Problems

Second Highest Salary II - Problem

Database Medium

Second Highest Salary II

Imagine you're working as a data analyst for a large corporation's HR department. Your company wants to identify high-performing employees who earn the second-highest salary in each department for potential promotions or special recognition.

Given an employees table with employee IDs, salaries, and departments, your task is to find all employees who earn the second-highest salary within their respective departments. If multiple employees share the same second-highest salary, include them all!

The Challenge:
• Some departments might have only one unique salary level (no second-highest)
• Multiple employees can have the same salary
• We need to rank salaries within each department separately

Goal: Return employee IDs and departments for all second-highest earners, ordered by employee ID ascending.

Input & Output

example_1.sql — Basic Department Analysis

$ Input: employees = [[1,70000,'Sales'],[2,80000,'Sales'],[3,80000,'Sales'],[4,90000,'Sales'],[5,55000,'IT'],[6,65000,'IT'],[7,65000,'IT']]

› Output: [[2,'Sales'],[3,'Sales'],[5,'IT']]

💡 Note: Sales: 90K(rank 1) > 80K(rank 2) > 70K(rank 3). Employees 2,3 both earn 80K (2nd highest). IT: 65K(rank 1) > 55K(rank 2). Employee 5 earns 55K (2nd highest).

example_2.sql — Department with Single Employee

$ Input: employees = [[1,50000,'HR'],[2,60000,'Finance'],[3,70000,'Finance']]

› Output: [[2,'Finance']]

💡 Note: HR has only one employee, so no second-highest salary exists. Finance: 70K(rank 1) > 60K(rank 2). Employee 2 earns the second-highest salary in Finance.

example_3.sql — All Employees Same Salary

$ Input: employees = [[1,50000,'Tech'],[2,50000,'Tech'],[3,50000,'Tech']]

› Output: []

💡 Note: All employees in Tech department earn the same salary (50K), so they all have rank 1. There is no second-highest salary, hence empty result.

Visualization

Tap to expand

Understanding the Visualization

Partition by Department

Separate employees into department-specific groups for independent analysis

Rank Salaries

Within each department, assign ranks based on salary (highest = rank 1)

Handle Ties

DENSE_RANK ensures employees with same salary get the same rank

Filter Second-Highest

Select only employees with rank 2 (second-highest salary)

Sort Results

Order final results by employee ID for consistent output

Key Takeaway

🎯 Key Insight: Window functions like DENSE_RANK() with PARTITION BY allow us to perform complex ranking operations across different groups in a single, efficient database operation, making department-wise salary analysis both elegant and performant.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting for ranking within partitions

⚡ Linearithmic

Space Complexity

O(n)

Space for ranking computation and result storage

⚡ Linearithmic Space

Constraints

1 ≤ employees.length ≤ 1000
1 ≤ emp_id ≤ 10⁴
1 ≤ salary ≤ 10⁶
1 ≤ dept.length ≤ 50
emp_id values are unique
dept consists of lowercase and uppercase English letters

Asked in

G Google 45 a Amazon 38 ⊞ Microsoft 32 f Meta 28 Apple 22 N Netflix 18

The optimal solution uses SQL window functions with DENSE_RANK() to efficiently rank salaries within each department. By partitioning data by department and ordering by salary descending, we can identify second-highest earners in a single table scan with O(n log n) time complexity. The DENSE_RANK function correctly handles tied salaries, ensuring all employees with the second-highest salary are included in the results.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with DENSE_RANK (Optimal)	O(n log n)	O(n)	Use DENSE_RANK() window function to rank salaries within each department
Brute Force (Multiple Subqueries)	O(n² log n)	O(n)	Use nested subqueries to find distinct salaries and filter for second-highest

Window Function with DENSE_RANK (Optimal) — Algorithm Steps

Use DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) to rank salaries
Partition by department to rank within each department separately
Order by salary descending so highest gets rank 1, second-highest gets rank 2
Filter results where rank = 2 to get second-highest earners
Order final results by emp_id ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by Department

Group employees by department for separate ranking

Order by Salary DESC

Sort salaries within each department (highest first)

Apply DENSE_RANK()

Assign ranks, handling ties correctly

Filter Rank = 2

Select only second-highest earners

Order by EmpID

Final sorting for consistent output

Code -

solution.c — C

// Window function simulation in C
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int emp_id;
    int salary;
    char dept[50];
} Employee;

typedef struct {
    int emp_id;
    char dept[50];
} Result;

int compare_salary_desc(const void* a, const void* b) {
    Employee* empA = (Employee*)a;
    Employee* empB = (Employee*)b;
    return empB->salary - empA->salary;
}

int compare_result(const void* a, const void* b) {
    Result* resA = (Result*)a;
    Result* resB = (Result*)b;
    return resA->emp_id - resB->emp_id;
}

int secondHighestSalaryOptimal(Employee* employees, int n, Result* result) {
    int result_count = 0;
    
    // Process each department
    char processed_depts[100][50];
    int processed_count = 0;
    
    for (int i = 0; i < n; i++) {
        // Check if department already processed
        int already_processed = 0;
        for (int j = 0; j < processed_count; j++) {
            if (strcmp(processed_depts[j], employees[i].dept) == 0) {
                already_processed = 1;
                break;
            }
        }
        if (already_processed) continue;
        
        strcpy(processed_depts[processed_count++], employees[i].dept);
        
        // Collect employees from this department
        Employee dept_emps[100];
        int dept_count = 0;
        
        for (int j = 0; j < n; j++) {
            if (strcmp(employees[j].dept, employees[i].dept) == 0) {
                dept_emps[dept_count++] = employees[j];
            }
        }
        
        // Sort by salary descending
        qsort(dept_emps, dept_count, sizeof(Employee), compare_salary_desc);
        
        // Assign dense ranks
        int ranks[100];
        int current_rank = 1;
        int prev_salary = -1;
        
        for (int j = 0; j < dept_count; j++) {
            if (prev_salary != -1 && dept_emps[j].salary != prev_salary) {
                current_rank++;
            }
            
            // Find original employee and assign rank
            for (int k = 0; k < dept_count; k++) {
                if (dept_emps[k].emp_id == dept_emps[j].emp_id) {
                    ranks[k] = current_rank;
                    break;
                }
            }
            prev_salary = dept_emps[j].salary;
        }
        
        // Find rank 2 employees
        for (int j = 0; j < dept_count; j++) {
            if (ranks[j] == 2) {
                result[result_count].emp_id = dept_emps[j].emp_id;
                strcpy(result[result_count].dept, dept_emps[j].dept);
                result_count++;
            }
        }
    }
    
    qsort(result, result_count, sizeof(Result), compare_result);
    return result_count;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting for ranking within partitions

⚡ Linearithmic

Space Complexity

O(n)

Space for ranking computation and result storage

⚡ Linearithmic Space

Constraints

1 ≤ employees.length ≤ 1000
1 ≤ emp_id ≤ 10⁴
1 ≤ salary ≤ 10⁶
1 ≤ dept.length ≤ 50
emp_id values are unique
dept consists of lowercase and uppercase English letters

42.3K Views

High Frequency

~18 min Avg. Time

1.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

Window Function with DENSE_RANK (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler