Welcome to Subscribe On Youtube

185. Department Top Three Salaries

Description

Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference column) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

 

Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID of a department and its name.

 

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write a solution to find the employees who are high earners in each of the departments.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
Explanation: 
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees

Solutions

• Python
• SQL

• import pandas as pd
  
  
  def top_three_salaries(
      employee: pd.DataFrame, department: pd.DataFrame
  ) -> pd.DataFrame:
      salary_cutoff = (
          employee.drop_duplicates(["salary", "departmentId"])
          .groupby("departmentId")["salary"]
          .nlargest(3)
          .groupby("departmentId")
          .min()
      )
      employee["Department"] = department.set_index("id")["name"][
          employee["departmentId"]
      ].values
      employee["cutoff"] = salary_cutoff[employee["departmentId"]].values
      return employee[employee["salary"] >= employee["cutoff"]].rename(
          columns={"name": "Employee", "salary": "Salary"}
      )[["Department", "Employee", "Salary"]]
  
  

• # Write your MySQL query statement below
  SELECT
      d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
  FROM
      Employee e1
  JOIN
      Department d
  ON e1.DepartmentId = d.Id
  WHERE
      3 > (
          SELECT
              COUNT(DISTINCT e2.Salary)
          FROM
              Employee e2
          WHERE
              e2.Salary > e1.Salary
          AND e1.DepartmentId = e2.DepartmentId
          )
  ;
  
  
  -- use group by
  
  SELECT
  D.Name as Department, E.Name as Employee, E.Salary
  FROM
  Department D, Employee E, Employee E2
  WHERE
      D.ID = E.DepartmentId
      AND E.DepartmentId = E2.DepartmentId
      AND E.Salary <= E2.Salary
  GROUP BY D.ID,E.Name
  HAVING COUNT(DISTINCT E2.Salary) <= 3
  ORDER BY D.Name, E.Salary DESC
  
  -- 
  
  WITH
      T AS (
          SELECT
              *,
              DENSE_RANK() OVER (
                  PARTITION BY departmentId
                  ORDER BY salary DESC
              ) AS rk
          FROM Employee
      )
  SELECT d.name AS Department, t.name AS Employee, salary AS Salary
  FROM
      T AS t
      JOIN Department AS d ON t.departmentId = d.id
  WHERE rk <= 3;
  
  

All Problems

All Solutions