LeetCode 185. Department Top Three Salaries SQL Solution

Problem

LeetCode SQL Problem

185. Department Top Three Salaries

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

Solution - Dense_Rank() Window Function

MySQL

Department Top Three Salaries

WITH salary_analysis
AS (
	SELECT d.Name AS Department,
		e.Name AS Employee,
		e.Salary,
		dense_rank() OVER (
			PARTITION BY d.Id ORDER BY e.Salary DESC
			) AS salary_rank_num
	FROM Department d
	INNER JOIN Employee e ON d.Id = e.DepartmentId
	)
SELECT Department,
	Employee,
	Salary
FROM salary_analysis
WHERE salary_rank_num <= 3
ORDER BY Department,
	Salary DESC;

TSQL

Department Top Three Salaries

WITH salary_analysis
AS (
	SELECT d.Name AS Department,
		e.Name AS Employee,
		e.Salary,
		dense_rank() OVER (
			PARTITION BY d.Id ORDER BY e.Salary DESC
			) AS salary_rank_num
	FROM Department d
	INNER JOIN Employee e ON d.Id = e.DepartmentId
	)
SELECT Department,
	Employee,
	Salary
FROM salary_analysis
WHERE salary_rank_num <= 3
ORDER BY Department,
	Salary DESC;

This SQL query uses a common table expression (CTE) named salary_analysis to calculate the rank of each employee's salary within their respective department. The dense_rank() function is used to assign a rank number to each employee's salary, with the highest salary having rank number 1, the second-highest having rank number 2, and so on.

The CTE joins the Department and Employee tables on their respective Id and DepartmentId columns. It selects the Name column from the Department table and the Name and Salary columns from the Employee table, as well as the salary rank number calculated using the dense_rank() function.

The main query selects the Department, Employee, and Salary columns from the salary_analysis CTE, but only for employees whose salary rank number is less than or equal to 3. This filters the results to show only the top three salaries for each department. The results are then sorted by department name and salary in descending order.

Solution - Subquery In Where Clause

MySQL

Department Top Three Salaries

SELECT D.Name AS Department
	,E.Name AS Employee
	,E.Salary
FROM Department AS D
INNER JOIN Employee AS E ON D.Id = E.DepartmentId
WHERE (
		SELECT COUNT(DISTINCT I.Salary)
		FROM Employee AS I
		WHERE I.DepartmentId = D.Id
			AND I.Salary > E.Salary
		) <= 2
ORDER BY D.Name
	,E.Salary DESC;

TSQL

Department Top Three Salaries

SELECT D.Name AS Department
	,E.Name AS Employee
	,E.Salary
FROM Department AS D
INNER JOIN Employee AS E ON D.Id = E.DepartmentId
WHERE (
		SELECT COUNT(DISTINCT I.Salary)
		FROM Employee AS I
		WHERE I.DepartmentId = D.Id
			AND I.Salary > E.Salary
		) <= 2
ORDER BY D.Name
	,E.Salary DESC;

This SQL query retrieves the name of the department, employee, and salary for employees whose salary is not among the top two highest in their department.

The query begins by selecting the Name column from the Department table and the Name and Salary columns from the Employee table, using the INNER JOIN keyword to join the two tables on the Id and DepartmentId columns.

The WHERE clause is used to filter the results. It includes a subquery that selects the count of distinct salaries greater than the salary of the current employee within their department. If this count is less than or equal to 2, the employee is included in the results.

Finally, the results are sorted in ascending order by department name and in descending order by salary.

Query Output

Department	Employee	Salary
IT	Max	90000
IT	Joe	85000
IT	Randy	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000