Example 3: Employee Ranking

This SQL query ranks employees within each department based on their salary in descending order. It returns the department ID, employee ID, salary, and the rank of each employee within their respective department.

SELECT department_id, 
       employee_id, 
       salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
ORDER BY department_id, rank;

Query Breakdown:

1. SELECT Clause:
   • department_id: Retrieves the ID of the department.
   • employee_id: Retrieves the ID of the employee.
   • salary: Retrieves the salary of the employee.
   • RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank: This is the window function calculating the rank of each employee within their department.
2. Window Function Explanation:
   • RANK(): This is the window function used to assign ranks to rows within each partition.
   • OVER: Defines the window of rows for the function.
     • PARTITION BY department_id: Divides the result set into partitions based on department_id. Each department is treated as a separate group.
     • ORDER BY salary DESC: Orders the rows within each partition by salary in descending order, assigning higher ranks to higher salaries.
3. FROM Clause:
   • FROM employees: Specifies the table from which to retrieve the data.
4. ORDER BY Clause:
   • ORDER BY department_id, rank: Orders the final result set first by department_id and then by rank within each department.

Detailed Step-by-Step Execution

Initial State: Assume the employees table has the following

Processing Each Department:

• Department 1:
  • Employee 102 and Employee 103 both have a salary of 60000:
    • Rank = 1 (both have the same rank)
  • Employee 101 has a salary of 50000:
    • Rank = 3 (next available rank after 1, skipping rank 2 due to tie above)
• Department 2:
  • Employee 202 has a salary of 55000:
    • Rank = 1
  • Employee 203 has a salary of 50000:
    • Rank = 2
  • Employee 201 has a salary of 45000:
    • Rank = 3

Final Output: