Introduction

When working with employee data, you may need to rank employees based on their salaries within each department.

For example, the HR team may want to identify the highest-paid employee in every department.

SQL window functions make this task easier. In this challenge, we will use "ROW_NUMBER()" with "PARTITION BY" and "ORDER BY" to generate department-wise salary rankings.

The Problem

You have an "employees" table containing employee IDs, employee names, departments and salaries.

Your task is to rank employees based on their salaries within each department, starting with the highest-paid employee.

employees

emp_id| emp_name| department| salary
101| Amit| IT| 90000
102| Rahul| IT| 75000
103| Priya| HR| 80000
104| Neha| HR| 70000
105| Karan| IT| 85000
106| Sneha| HR| 65000

Your Challenge

Write a SQL query to:

1. Display employee name, department and salary.
2. Rank employees based on salary within each department.
3. Assign rank 1 to the highest-paid employee.
4. Restart the ranking for each department.
5. Keep the original table unchanged.

Think about the solution before looking below!

SQL Solution (Oracle)

SELECT
emp_id,
emp_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

Expected Output

emp_id| emp_name| department| salary| salary_rank
103| Priya| HR| 80000| 1
104| Neha| HR| 70000| 2
106| Sneha| HR| 65000| 3
101| Amit| IT| 90000| 1
105| Karan| IT| 85000| 2
102| Rahul| IT| 75000| 3

How Does This Query Work?

Let's understand the important parts.

1. ROW_NUMBER()

ROW_NUMBER()

It assigns a unique sequential number to each row, starting from 1.

1. PARTITION BY department

PARTITION BY department

This divides employees into separate department groups.

The numbering starts again from 1 for every department.

For example:

• HR employees get ranks 1, 2 and 3.
• IT employees also get ranks 1, 2 and 3.

1. ORDER BY salary DESC

ORDER BY salary DESC

This sorts employees from the highest salary to the lowest salary within each department.

1. AS salary_rank

This gives a meaningful name to the calculated ranking column.

One Thing to Remember

"ROW_NUMBER()" assigns a different number to every row, even when two employees have the same salary.

If you want employees with equal salaries to receive the same rank, you should explore "RANK()" and "DENSE_RANK()".

Also, if multiple employees have the same salary and you want consistent row numbering, you can add "emp_id" as a secondary sorting column.

For example:

ORDER BY salary DESC, emp_id

ROW_NUMBER() vs RANK() vs DENSE_RANK()

These three window functions are commonly used for ranking data.

Function| Behaviour
ROW_NUMBER()| Assigns a unique number to every row
RANK()| Gives the same rank to tied values and skips the next rank
DENSE_RANK()| Gives the same rank to tied values without skipping ranks

Understanding the difference is useful when preparing salary reports, sales rankings and interview queries.

Your Turn

What would happen if we replaced "ROW_NUMBER()" with "RANK()" or "DENSE_RANK()"?

Can you modify this query to find the top two highest-paid employees in each department?

Share your SQL query in the comments.

Follow Sankalan Data Tech for more practical SQL challenges, examples and database learning resources.

SQL #OracleSQL #DataAnalytics #Programming