<aside> 🪟 Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does.

</aside>

Syntax Structure

function_name() OVER (
    PARTITION BY column    -- divide rows into groups (optional)
    ORDER BY column        -- sort within partition (optional)
    ROWS BETWEEN ...       -- define the window frame (optional)
)

1. OVER() — Window Over All Rows

SELECT
    OrderID, OrderDate, Sales,
    SUM(Sales) OVER () AS TotalSales   -- sum of ALL rows
FROM Sales.Orders;

2. PARTITION BY — Divide into Groups

SELECT
    OrderID, ProductID, Sales,
    SUM(Sales) OVER ()                           AS TotalSales,
    SUM(Sales) OVER (PARTITION BY ProductID)     AS SalesByProduct
FROM Sales.Orders;

3. ORDER BY in Window — Running Totals

-- Cumulative (running) total of sales by product
SELECT
    OrderID, ProductID, OrderDate, Sales,
    SUM(Sales) OVER (
        PARTITION BY ProductID
        ORDER BY OrderDate
    ) AS RunningTotal
FROM Sales.Orders;

4. FRAME Clause

-- Current row and 2 following rows
SUM(Sales) OVER (
    PARTITION BY OrderStatus
    ORDER BY OrderDate
    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
)

-- Previous 2 rows and current row
SUM(Sales) OVER (
    PARTITION BY OrderStatus
    ORDER BY OrderDate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- From start to current row (cumulative)
SUM(Sales) OVER (
    PARTITION BY OrderStatus
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

5. Window Ranking Functions

SELECT
    OrderID, ProductID, Sales,
    ROW_NUMBER()  OVER (ORDER BY Sales DESC) AS RowNum,    -- unique always
    RANK()        OVER (ORDER BY Sales DESC) AS Rank,      -- gaps on ties
    DENSE_RANK()  OVER (ORDER BY Sales DESC) AS DenseRank, -- no gaps on ties
    NTILE(4)      OVER (ORDER BY Sales DESC) AS Quartile,  -- divide into buckets
    CUME_DIST()   OVER (ORDER BY Sales DESC) AS CumeDist   -- cumulative distribution
FROM Sales.Orders;