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.


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
    SELECT DepartmentId, Name, Salary,
           DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
    FROM Employee
) AS RankedEmployees
WHERE rank <= 3;


DepartmentId Name Salary
101 Bob 55000
101 Alice 50000


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.