Window Functions

What are Window Functions?

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions (GROUP BY), window functions do not collapse rows.

Syntax

FUNCTION_NAME() OVER (
    [PARTITION BY column]
    [ORDER BY column]
)

Common Window Functions

1. ROW_NUMBER()

Assigns a unique sequential number to each row.

SELECT Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) as Rank
FROM Employees;

2. RANK() vs DENSE_RANK()

• RANK(): Skips numbers if there's a tie (1, 1, 3).
• DENSE_RANK(): Does not skip numbers (1, 1, 2).

3. PARTITION BY

Divides the result set into partitions (groups) and applies function to each partition independently.

SELECT Name, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as DeptRank
FROM Employees;

Ranks employees by salary WITHIN their own department.