Window Functions in SQL: RANK, ROW_NUMBER, and LEAD Explained

Concept in Simple Words: Regular aggregate functions (like SUM or AVG) collapse multiple rows into a single summary row. Window functions perform calculations across a group of related rows, but they do NOT collapse the rows. Each row retains its individual identity while displaying the computed metric.

Deep Walkthrough & Code: Let's look at how ROW_NUMBER(), RANK(), and DENSE_RANK() assign ranking integers based on order criteria:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
       RANK() OVER (ORDER BY salary DESC) as rk,
       DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rk
FROM employees;

Step-by-Step Dry Run: Suppose we have salaries [100k, 100k, 80k].
- Row 1 (100k): row_num = 1, rk = 1, dense_rk = 1.
- Row 2 (100k): row_num = 2 (unique index), rk = 1 (tie), dense_rk = 1 (tie).
- Row 3 (80k): row_num = 3, rk = 3 (skips 2 due to rank tie), dense_rk = 2 (next consecutive integer).
Understanding how rankings handle ties is a common interview screening question.

Production Level Issue & Fix: Running window functions without a PARTITION BY clause on tables with millions of rows forces the database to load the entire dataset into a single memory block to sort it, leading to disk-spill slowdowns. In production, always filter the dataset in a subquery or CTE first to reduce the volume of rows processed by the window function.