Learn How To Write SQL Queries With Examples: #3

Without big data analytics, companies are blind and deaf, wandering out onto the web like deer on a freeway.

Geoffrey Moore, management consultant and author of Crossing the Chasm
Question Source: LeetCode
Solution Language: MySQL

This Q&A series will cover data questions from LeetCode and present my solutions to them. Please feel free to comment with your suggestions if you feel that these problems may be solved in a more optimized manner.

Question (LeetCode Question #185, Level: Hard)

Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| Id           | int     |
| Name         | varchar |
| Salary       | int     |
| DepartmentId | int     |
+--------------+---------+
Id is the primary key for this table.
Each row contains the ID, name, salary, and department of one employee.

Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| Id          | int     |
| Name        | varchar |
+-------------+---------+
Id is the primary key for this table.
Each row contains the ID and the name of one department.

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 an SQL query to find the employees who are high earners in each of the departments.

Return the result table in any order.

The query result format is in the following example:

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 |
+----+-------+

Result table:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

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

Solution

WITH temp AS
(SELECT Name AS Employee,
Salary,
DepartmentId,
DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rnk
FROM Employee)
SELECT d.Name AS Department,
temp.Employee,
temp.Salary
FROM temp JOIN Department d
ON temp.DepartmentId = d.Id
WHERE temp.rnk<=3

Difference between Rank() and Dense_Rank() Window Functions

In the above solution, I have used the Dense_Rank() function instead of the Rank() function. This is because the question describes a high earner in a department as “an employee who has a salary in the top three unique salaries for that department”.

To better illustrate the difference, check out the rank allocated to each employee in both cases (Note: I have displayed rank for all employees instead of just the top 3):

Result table:
+----+----------+--------+--------------+--------+--------------+
| Id | Employee | Salary | DepartmentId | Rank() | Dense_Rank() |
+----+----------+--------+--------------+--------+--------------+
| 4  | Max      | 90000  |       1      |   1    |       1      |
| 1  | Joe      | 85000  |       1      |   2    |       2      |
| 6  | Randy    | 85000  |       1      |   2    |       2      |
| 7  | Will     | 70000  |       1      |   4    |       3      |
| 5  | Janet    | 69000  |       1      |   5    |       4      |
| 2  | Henry    | 80000  |       2      |   1    |       1      |
| 3  | Sam      | 60000  |       2      |   2    |       2      |
+----+----------+--------+--------------+--------+--------------+

As you can see, in case of Rank(), a rank is skipped after the same ranks:
Joe and Randy rank 2 in Department 1, but Will ranks 4 instead of 3 (a rank is skipped).

But we needed Will to rank 3 instead of 4, so that we can filter by the condition rank<=3 to get employees whose salaries are in the top 3 distinct salaries in each department.

For achieving this goal, we use Dense_Rank() function. As you can see above, Will ranks 3 instead of 4 in the Dense_Rank() column

Learn How To Write SQL Queries With Examples: #2

Data Analytics Visual

Data Is A Precious Thing And Will Last Longer Than The Systems Themselves.

Sir Tim Berners-Lee (The inventor of the World Wide Web)
Question Source: LeetCode
Solution Language: MySQL

This Q&A series will cover data questions from LeetCode and present my solutions to them. Please feel free to comment with your suggestions if you feel that these problems may be solved in a more optimized manner.

Question (LeetCode Question #177, Level: Medium)

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Solution

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
Select
CASE when count(distinct Salary) <= N then null
else
(select distinct Salary
from Employee
order by Salary desc limit 1 offset N)
end
from Employee
);
END

NOTE:
  • MySQL LIMIT and OFFSET syntax can only take numeric constants
  • LIMIT 1 OFFSET N can also be written as LIMIT N,1

Question (LeetCode Question #184, Level: Medium)

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

Solution

Approach 1:

WITH temp AS
(SELECT d.Id, d.Name, MAX(e.Salary) AS Salary
FROM Employee e JOIN Department d
ON e.DepartmentId = d.Id
GROUP BY 1,2
)
SELECT temp.Name AS Department, Employee.Name AS Employee, Employee.Salary
FROM Employee JOIN temp
ON Employee.DepartmentId = temp.Id
WHERE Employee.Salary = temp.Salary;

Approach 2:

WITH temp AS (
SELECT DepartmentId, Name, Salary,
RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rnk
FROM Employee)
SELECT d.Name AS Department, temp.NAME AS Employee, temp.SALARY AS Salary
FROM temp
JOIN Department d
ON d.Id = temp.DepartmentId AND rnk = 1

Approach 3

SELECT Department, Employee, Salary
FROM (SELECT
d.Name AS Department,
e.Name AS Employee,
Salary,
RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS rnk
FROM Employee e JOIN Department d
ON e.DepartmentId = d.Id) AS temp
WHERE rnk = 1

NOTE:
  • Approach 2 and 3 use “Window Functions” to solve this problem.
  • Window functions are used to optimize queries for efficiency and reduce query complexity when querying large datasets.
  • To learn more about the Window functions used in MySQL 8.0, click here.
  • Another source for learning about Window functions is Mode Analytics.