Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. They differ from aggregate functions in that they do not cause rows to become grouped into a single output row; the rows retain their separate identities.

Syntax Overview

function_name ( [ expression ] ) 
OVER ( 
    [PARTITION BY partition_expression] 
    [ORDER BY order_expression [ASC|DESC]] 
    [ROWS|RANGE frame_specification] 
)

Common Window Functions

Function	Description
`ROW_NUMBER()`	Returns the sequential integer of the row within the partition.
`RANK()`	Returns the rank of each row with gaps for ties.
`DENSE_RANK()`	Rank without gaps.
`NTILE(n)`	Divides the rows into n buckets.
`LAG(value_expression [, offset] [, default])`	Accesses data from a preceding row.
`LEAD(value_expression [, offset] [, default])`	Accesses data from a following row.
`FIRST_VALUE(value_expression)`	Returns the first value in the window frame.
`LAST_VALUE(value_expression)`	Returns the last value in the window frame.
`NTH_VALUE(value_expression, n)`	Returns the n‑th value in the window frame.

Examples

1. Ranking Employees by Salary

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

2. Using LAG to Compare Current and Previous Sales

SELECT 
    SaleDate,
    SalesAmount,
    LAG(SalesAmount,1,0) OVER (ORDER BY SaleDate) AS PrevDaySales,
    SalesAmount - LAG(SalesAmount,1,0) OVER (ORDER BY SaleDate) AS Diff
FROM DailySales;

3. Cumulative Sum with ROWS UNBOUNDED PRECEDING

SELECT 
    OrderID,
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate 
                      ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Orders;