Mastering SQL Window Functions: Rank, Row Number, and Analytics

Window functions perform calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, window functions do not collapse rows into a single output row - they retain the details of individual entries while projecting statistical values.

ROW_NUMBER vs RANK vs DENSE_RANK

These three window functions assign integers based on the sort order in the OVER clause. Their behavior on tied values differs:

- ROW_NUMBER(): Assigns unique, sequential numbers starting at 1. Ties are resolved arbitrarily. - RANK(): Assigns duplicate ranks to identical values. If ties exist, it skips subsequent rank numbers. - DENSE_RANK(): Assigns duplicate ranks to identical values, but does not skip any numbers.

-- Compare rankings of employee salaries
SELECT Name, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) as RowNum,
       RANK() OVER (ORDER BY Salary DESC) as RankVal,
       DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRankVal
FROM Employee;

Analytical Offsets: LEAD and LAG

LEAD and LAG let you access data from relative rows in the window without performing self-joins. LAG retrieves a value from N rows before the current row, and LEAD retrieves a value N rows ahead.