SQL Window Functions

SELECT 
    DepartmentID,
    FirstName,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum,
    RANK()       OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankNum,
    DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS# SQL Window Functions

A **window function** performs calculations across a set of table rows that are related to the current row. Unlike aggregate functions (`SUM`, `AVG`, `COUNT`), which collapse multiple rows into a single summary row using `GROUP BY`, window functions retain the individual identity of each row while computing aggregate or ranking values alongside it.

The OVER() Clause

The defining feature of a window function is the OVER() clause. It defines the "window" of rows that the function operates on.

Syntax

function_name(expression) OVER (
    [PARTITION BY partition_column]
    [ORDER BY sort_column]
    [ROWS|RANGE frame_spec]
)

• PARTITION BY: Divides the query result set into partitions (groups). The window function is calculated independently for each partition. If omitted, the function treats the entire result set as a single partition.
• ORDER BY: Specifies the logical sorting order of rows within each partition.
• ROWS|RANGE: Defines the frame boundaries relative to the current row (e.g., rolling averages).

How Window Functions Differ from Aggregates

               AGGREGATE FUNCTION (GROUP BY)
+------------+------------+           +------------+------------+
| Department | Salary     |           | Department | SUM(Salary)|
+------------+------------+           +------------+------------+
| Sales      | 5000       |  ----->   | Sales      | 11000      |
| Sales      | 6000       |           | IT         | 15000      |
| IT         | 7000       |           +------------+------------+
| IT         | 8000       |           (Collapses 4 rows into 2)
+------------+------------+

               WINDOW FUNCTION (OVER PARTITION BY)
+------------+------------+---------------------------------+
| Department | Salary     | SUM(Salary) OVER (PARTITION...) |
+------------+------------+---------------------------------+
| Sales      | 5000       | 11000                           |
| Sales      | 6000       | 11000                           |
| IT         | 7000       | 15000                           |
| IT         | 8000       | 15000                           |
+------------+------------+---------------------------------+
(Keeps all 4 rows intact)

Categories of Window Functions

1. Ranking Functions

Ranking functions assign a sequential integer or rank to each row based on its ordering within a partition.

• ROW_NUMBER(): Assigns a unique sequential integer starting at 1 for each row in a partition. Ties receive distinct numbers.
• RANK(): Assigns ranks with gaps. If two rows tie for rank 1, both receive 1, and the next row receives rank 3.
• DENSE_RANK(): Assigns ranks without gaps. If two rows tie for rank 1, both receive 1, and the next row receives rank 2.
• NTILE(n): Divides rows within a partition into $n$ roughly equal buckets and assigns the bucket number (1 to $n$).

Example: Comparing Ranking Functions

SELECT 
    EmployeeID,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
    RANK()       OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankNum,
    DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;

Output:

2. Value / Analytic Functions

Value functions allow you to access data from other rows in the result set relative to the current row without performing a self-join.

• LAG(column, offset): Accesses data from a row at a specified physical offset before the current row.
• LEAD(column, offset): Accesses data from a row at a specified physical offset after the current row.
• FIRST_VALUE(column): Returns the value from the first row of the window frame.
• LAST_VALUE(column): Returns the value from the last row of the window frame.

Example: Calculating Year-Over-Year Growth with LAG()

SELECT 
    SalesYear,
    TotalRevenue,
    LAG(TotalRevenue, 1) OVER (ORDER BY SalesYear) AS PreviousYearRevenue,
    TotalRevenue - LAG(TotalRevenue, 1) OVER (ORDER BY SalesYear) AS YoYChange
FROM AnnualSales;

3. Aggregate Window Functions

Standard aggregate functions (SUM, AVG, COUNT, MIN, MAX) can be turned into window functions by adding an OVER() clause.

Example: Running Total (Cumulative Sum)

When an ORDER BY clause is included inside OVER() with an aggregate function, SQL defaults to computing a cumulative running total from the first row of the partition up to the current row.

SELECT 
    OrderID,
    OrderDate,
    TotalAmount,
    SUM(TotalAmount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

Window Frames: ROWS vs. RANGE

When using an ORDER BY clause inside OVER(), you can refine the exact subset of rows included in the window using frame specifications.

• ROWS BETWEEN lower_bound AND upper_bound: Operates on physical row counts.
• RANGE BETWEEN lower_bound AND upper_bound: Operates on logical value boundaries.

Common Frame Options:

• UNBOUNDED PRECEDING: Starts at the first row of the partition.
• CURRENT ROW: Ends or starts at the active row.
• n PRECEDING: Looks back $n$ rows.
• n FOLLOWING: Looks forward $n$ rows.

Example: 3-Period Moving Average

SELECT 
    SalesDate,
    DailyRevenue,
    AVG(DailyRevenue) OVER (
        ORDER BY SalesDate 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Day
FROM DailySales;