176. Second Highest Salary – Leetcode Solutions

Description

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

The result format is in the following example.

Examples:

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+
Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

Solution in Pandas

To solve the problem of finding the second highest salary in the Employee table using Pandas, we need to follow these steps:

1. Sort the Salaries: Sort the unique salary values in descending order.
2. Find the Second Highest Salary: Select the second element from the sorted list. If there are fewer than two unique salaries, return None.
3. Create the Result DataFrame: Return the result in the specified format.

import pandas as pd

def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:
    # Drop duplicates to get unique salaries
    unique_salaries = employee['salary'].drop_duplicates()
    
    # Sort the unique salaries in descending order
    sorted_salaries = unique_salaries.sort_values(ascending=False)
    
    # Check if there are at least two unique salaries
    if len(sorted_salaries) < 2:
        second_highest = None
    else:
        # Get the second highest salary
        second_highest = sorted_salaries.iloc[1]
    
    # Create the result DataFrame
    result = pd.DataFrame({'SecondHighestSalary': [second_highest]})
    
    return result

Explanation:

1. Drop Duplicates: employee['salary'].drop_duplicates() is used to remove duplicate salary values from the salary column. This ensures we only consider unique salaries.
2. Sort Salaries: unique_salaries.sort_values(ascending=False) sorts the unique salaries in descending order. This makes it easy to pick the second highest salary by indexing.
3. Check for Second Highest Salary:
   • If there are fewer than two unique salaries (len(sorted_salaries) < 2), set second_highest to None.
   • Otherwise, get the second highest salary using sorted_salaries.iloc[1], which selects the second element in the sorted series.
4. Create Result DataFrame: pd.DataFrame({'SecondHighestSalary': [second_highest]}) constructs a DataFrame with the required column name and the computed second highest salary.

This function will correctly handle the case where there is no second highest salary by returning None in the result DataFrame.

Solution in MySQL

-- Select the second highest salary
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Solution in PostgreSQL

-- Use a common table expression (CTE) to rank salaries
WITH RankedSalaries AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
)
-- Select the second highest salary or return NULL if it does not exist
SELECT 
    (SELECT salary 
     FROM RankedSalaries 
     WHERE rank = 2
     LIMIT 1) AS SecondHighestSalary;