LeetCode 176 – Second Highest Salary (SQL & Python) Solutions
This article is going to provide the solution to LeetCode 176. It’s a medium difficulty question.
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.
If you want to skip the article, check out the full YouTube video where I code the answer live.
LeetCode 176 SQL Solution
with sal_ranking as ( SELECT salary, DENSE_RANK() over (Order by salary desc) as sal_rank from Employee)
SELECT max(salary) as SecondHighestSalaryfrom sal_rankingwhere sal_rank = 2The code starts with a common table expression (CTE) named ‘sal_ranking’. A CTE is a temporary result set that you can reference within the context of a single SQL statement.
It selects the salary column from the Employee table and assigns a ranking to each salary using the DENSE_RANK() window function.
The DENSE_RANK() function is used to assign a unique rank to each distinct salary value, with no gaps in the ranking sequence.
The Order by salary desc clause orders the salaries in descending order, so the highest salary will have a rank of 1, the second-highest salary will have a rank of 2, and so on.
After defining the sal_ranking CTE, the main query selects the maximum salary from the sal_ranking CTE where the salary’s rank is 2. This effectively retrieves the second-highest salary from the Employee table.
LeetCode 176 Python Solution
import pandas as pd
def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame: employee = employee.drop_duplicates(subset=['salary']) if len(employee) < 2: return pd.DataFrame({'SecondHighestSalary': [None]}) else: employee['sal_rank'] = employee['salary'].rank(ascending=False) second_highest = employee[employee['sal_rank'] == 2] second_highest = second_highest.rename(columns={'salary': 'SecondHighestSalary'}) return second_highest[['SecondHighestSalary']]This function takes a pandas DataFrame employee containing salary information as input and returns a DataFrame containing the second-highest salary.
In the first line of the else condition, the ‘sal_rank’ column is added to the DataFrame, containing the rank of each salary in descending order (highest salary has rank 1, second-highest has rank 2, and so on).
The next line filters the DataFrame to select rows where the ‘sal_rank’ column equals 2, thus selecting the row(s) corresponding to the second-highest salary.
The column containing the salary values is renamed to ‘SecondHighestSalary’ to match the expected output.
Finally, the function returns a DataFrame containing only the ‘SecondHighestSalary’ column, which holds the value of the second-highest salary.
