Query Explanation - 1: Retrieving the Top 5 Highest-Paid Employees for Each Department

 

In this blog, we will break down how to retrieve the top 5 highest-paid employees from each department using SQL. This is a common query when working with databases that store information about employees and departments, and it involves ranking employees by their salary and returning the top earners for each department.

Problem Statement:

We want to retrieve the top 5 highest-paid employees in each department, sorted by salary in descending order. This means for each department, we need to rank employees by their salary, then limit the results to the top 5 for that department.

Example Schema:

Assume we have the following two tables:

1. Employee:

   • EmployeeID (Primary Key)
   • FirstName
   • LastName
   • Salary
   • DepartmentID (Foreign Key)

2. Department:

   • DepartmentID (Primary Key)
   • DepartmentName

SQL Query:

To solve this, we can use Common Table Expressions (CTEs) or Window Functions to rank employees by their salary within each department. The ROW_NUMBER() window function is particularly useful for this case, as it can assign a unique rank to each employee within a department, based on their salary.

Here’s how we can approach it:

WITH RankedEmployees AS (
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.Salary,
        d.DepartmentName,
        ROW_NUMBER() OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC) AS rank
    FROM 
        Employee e
    JOIN 
        Department d ON e.DepartmentID = d.DepartmentID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    DepartmentName
FROM 
    RankedEmployees
WHERE 
    rank <= 5
ORDER BY 
    DepartmentName,
    Salary DESC;

Detailed Breakdown:

1. Common Table Expression (CTE):

   • The WITH clause is used to define a temporary result set named RankedEmployees. This CTE allows us to simplify the query and avoid duplicating logic in the SELECT statement.

2. ROW_NUMBER() Function:

   • ROW_NUMBER() is a window function that assigns a unique row number to each row within a partition. Here, we partition the results by DepartmentID using the PARTITION BY clause. This means the row numbers will reset for each department.
   • The ORDER BY e.Salary DESC ensures that the highest salary gets the row number 1, the second highest salary gets 2, and so on.

3. Partitioning by Department:

   • The PARTITION BY e.DepartmentID groups the rows by department, so that the row numbers are applied separately for each department. This ensures that each department’s employees are ranked based on their salary, independent of other departments.

4. Filtering Top 5 Employees:

   • In the final SELECT query, we filter the results to only include employees where rank <= 5. This limits the result to the top 5 employees in each department.

5. Sorting the Results:

   • The results are sorted by DepartmentName and then by Salary in descending order, which makes it easier to see the top-paid employees in each department in a structured format.

Example Output:

Let's say we have the following data in the Employee and Department tables:

After executing the query, we will retrieve the top 5 highest-paid employees in each department, based on their salary, ordered by the department and salary.

Key Takeaways:

• Window Functions like ROW_NUMBER() are powerful for ranking and partitioning data without needing subqueries or multiple JOIN operations.
• The use of PARTITION BY allows you to rank data within specific groups (in this case, departments) while applying ranking logic across all rows.
• This type of query is often used in real-world scenarios where businesses need to retrieve the top performers, highest-paid employees, or best-selling products by category.

Feel free to try this query in your own environment and see how the ROW_NUMBER() function can simplify complex ranking tasks in SQL.