Department Top Three Salaries SQL Challenge - MySQL Solution

1. Introduction

This blog post examines how to identify the top three salaries in each department using MySQL. This kind of query is particularly useful in HR analytics for performance and compensation analysis.

Problem

The task is to write a MySQL query that lists the top three salaries in each department of a company.

Database Table:

Employee Table:

Id	Name	Salary	DepartmentId
1	John	1000	101
2	Jane	2000	101

Department Table:

Id	Name
101	Sales

3. MySQL Solution

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM (
    SELECT Name, Salary, DepartmentId,
           DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
    FROM Employee
) e
JOIN Department d ON e.DepartmentId = d.Id
WHERE e.rank <= 3;

Output:

Department	Employee	Salary
Sales	Jane	2000
Sales	John	1000

Explanation:

1. The query uses a window function (DENSE_RANK()) to rank the salaries within each department.

2. It partitions the data by DepartmentId and orders by Salary in descending order.

3. The outer query then joins this ranked data with the Department table.

4. The WHERE clause limits the results to employees with a rank of 3 or less, effectively showing the top three salaries in each department.