Window Functions and Ranking

Introduction

Window functions in SQL allow you to perform calculations across a set of rows related to the current row without collapsing the result set. Unlike GROUP BY, which aggregates rows into a single output, window functions retain individual rows while adding calculated values.

Ranking functions are a subset of window functions that assign ranks to rows based on specific conditions, making them useful for scenarios like leaderboards, top performers, and percentile calculations.

What are Window Functions?

A window function operates over a partition (subset) of data while keeping all original rows in the result. The OVER() clause defines how the function is applied by specifying:

1. PARTITION BY – Divides the data into groups.
2. ORDER BY – Defines row order within the partition.

Basic Syntax:

window_function() OVER (
    PARTITION BY column_name  
    ORDER BY column_name
)

• PARTITION BY groups rows into separate partitions.
• ORDER BY determines row processing order.

Types of Window Functions

1. Ranking Functions

Ranking functions assign a unique rank to each row within a partition based on the ORDER BY clause.

a) RANK() – Assigns a rank, skipping numbers for duplicate values.

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

• Employees in the same department are ranked by salary (highest first).
• If two employees have the same salary, they get the same rank, but the next rank is skipped.

b) DENSE_RANK() – Similar to RANK(), but does not skip numbers for ties.

SELECT employee_id, department, salary,  
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank  
FROM employees;

• Unlike RANK(), DENSE_RANK() does not create gaps in ranking.

c) ROW_NUMBER() – Assigns a unique sequential number to each row.

SELECT employee_id, department, salary,  
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num  
FROM employees;

• Even if multiple rows have the same salary, they get different row numbers.

d) NTILE(n) – Divides data into n equal parts and assigns a group number.

SELECT employee_id, department, salary,  
       NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile  
FROM employees;

• The result is divided into 4 equal quartiles.

2. Aggregate Window Functions

Unlike standard aggregate functions, window aggregates retain individual rows while computing cumulative values.

a) SUM() – Running Total Calculation

SELECT order_id, customer_id, order_amount,  
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total  
FROM orders;

• Computes a cumulative sum of order_amount per customer.

b) AVG() – Moving Average Calculation

SELECT order_id, customer_id, order_amount,  
       AVG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg  
FROM orders;

• Calculates a moving average for the last three orders.

c) COUNT() – Row Count per Partition

SELECT department, employee_id,  
       COUNT(*) OVER (PARTITION BY department) AS dept_employee_count  
FROM employees;

• Shows how many employees are in each department.

3. LEAD() and LAG() – Accessing Previous and Next Rows

a) LAG() – Fetch Previous Row Data

SELECT employee_id, department, salary,  
       LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary  
FROM employees;

• Retrieves the salary of the previous row in the same department.

b) LEAD() – Fetch Next Row Data

SELECT employee_id, department, salary,  
       LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary  
FROM employees;

• Retrieves the salary of the next row in the same department.

Real-World Example: Sales Leaderboard

Suppose we want to rank sales representatives by total sales in each region.

SELECT region, sales_rep, total_sales,  
       RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS rank  
FROM sales;

• This assigns ranks based on total_sales, grouped by region.
• If two reps have the same sales, they share a rank, and the next rank is skipped.

Window Functions vs. GROUP BY

Use window functions when you need both aggregated and detailed data in the same query.

 Conclusion

Window functions provide advanced analytical capabilities in SQL without losing row-level details. They are essential for ranking, running totals, moving averages, and trend analysis. Understanding and using them efficiently can significantly improve query performance and reporting accuracy.