Welcome to Subscribe On Youtube

3338. Second Highest Salary II 🔒

Description

Table: employees

+++
\| emp_id           \| int     \|
\| salary           \| int     \|
\| dept             \| varchar \|
++--+
\| emp_id \| salary \| dept      \|
+--+--+--+

Output:

+--+--+
\| 2      \| Sales     \|
\| 3      \| Sales     \|
\| 5      \| IT        \|
\| 8      \| Marketing \|
+--+-----+

Explanation:

  • Sales Department:
    • Highest salary is 90000 (emp_id: 4)
    • Second-highest salary is 80000 (emp_id: 2, 3)
    • Both employees with salary 80000 are included
  • IT Department:
    • Highest salary is 65000 (emp_id: 6, 7)
    • Second-highest salary is 55000 (emp_id: 5)
    • Only emp_id 5 is included as they have the second-highest salary
  • Marketing Department:
    • Highest salary is 55000 (emp_id: 9)
    • Second-highest salary is 50000 (emp_id: 8)
    • Employee 8 is included
  • HR Department:
    • Only has one employee
    • Not included in the result as it has fewer than 2 employees

</div>

Solutions

Solution 1: Window Function

We can use the DENSE_RANK() window function to rank employees in each department by salary in descending order, and then filter out the employees with a rank of $2$.

  • import pandas as pd


def find_second_highest_salary(employees: pd.DataFrame) -> pd.DataFrame:
    employees["rk"] = employees.groupby("dept")["salary"].rank(
        method="dense", ascending=False
    )
    second_highest = employees[employees["rk"] == 2][["emp_id", "dept"]]
    return second_highest.sort_values(by="emp_id")


  • # Write your MySQL query statement below
WITH
    T AS (
        SELECT
            emp_id,
            dept,
            DENSE_RANK() OVER (
                PARTITION BY dept
                ORDER BY salary DESC
            ) rk
        FROM Employees
    )
SELECT emp_id, dept
FROM T
WHERE rk = 2
ORDER BY 1;

All Problems

All Solutions