Department Top Three Salaries SQL Challenge - PostgreSQL Solution

1. Introduction

This blog post will demonstrate how to find the top three highest salaries in each department using PostgreSQL. This kind of query is commonly used in HR and finance departments for salary analysis.

Problem

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

Database Table:

Employee Table:

Id	Name	Salary	DepartmentId
1	Alice	50000	101
2	Bob	55000	101

Department Table:

Id	Name
101	HR

3. PostgreSQL Solution

SELECT DepartmentId, Name, Salary
FROM (
    SELECT DepartmentId, Name, Salary,
           DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
    FROM Employee
) AS RankedEmployees
WHERE rank <= 3;

Output:

DepartmentId	Name	Salary
101	Bob	55000
101	Alice	50000

Explanation:

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

2. The data is partitioned by DepartmentId and ordered by Salary in descending order.

3. The subquery (RankedEmployees) ranks each employee's salary within their department.

4. The outer query then selects employees with a rank of 3 or less, effectively retrieving the top three salaries in each department.