Bits of .NET

Need running totals, rankings, or moving averages? Window functions do it in one query without complex self-joins or cursors.

The Old Painful Way – Self-Join:

-- Calculate running total of sales
SELECT 
    o1.OrderDate,
    o1.Amount,
    (SELECT SUM(o2.Amount) 
     FROM Orders o2 
     WHERE o2.OrderDate <= o1.OrderDate) AS RunningTotal
FROM Orders o1
ORDER BY o1.OrderDate;

-- Problems:
-- - Correlated subquery runs for EVERY row
-- - O(n²) performance (100K rows = 10 billion operations)
-- - Takes minutes on large tables

The Window Function Way:

-- Same result, 100x faster
SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders
ORDER BY OrderDate;

-- Performance: O(n) instead of O(n²)
-- 100K rows: 2 seconds vs 3 minutes

How Window Functions Work:

-- Syntax:
-- FUNCTION() OVER (
--     PARTITION BY column   -- Optional: Group by
--     ORDER BY column       -- Optional: Order within partition
--     ROWS/RANGE clause     -- Optional: Frame specification
-- )

-- Without PARTITION BY: Operates on entire result set
-- With PARTITION BY: Resets calculation per group
-- ORDER BY: Determines calculation order

Running Total Examples:

-- Simple running total
SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

-- Running total per customer
SELECT 
    CustomerId,
    OrderDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate
    ) AS CustomerRunningTotal
FROM Orders;

-- Running average
SELECT 
    OrderDate,
    Amount,
    AVG(Amount) OVER (ORDER BY OrderDate) AS RunningAverage
FROM Orders;

Ranking Functions:

-- ROW_NUMBER: Unique sequential number
SELECT 
    ProductName,
    Sales,
    ROW_NUMBER() OVER (ORDER BY Sales DESC) AS RowNum
FROM Products;
-- Output: 1, 2, 3, 4, 5... (no ties)

-- RANK: Same rank for ties, skips numbers
SELECT 
    ProductName,
    Sales,
    RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM Products;
-- Output: 1, 2, 2, 4, 5... (ties get same rank, next rank skips)

-- DENSE_RANK: Same rank for ties, doesn't skip
SELECT 
    ProductName,
    Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRank
FROM Products;
-- Output: 1, 2, 2, 3, 4... (ties get same rank, next rank doesn't skip)

-- Example with all three:
-- Sales: 100, 90, 90, 80, 70
-- ROW_NUMBER: 1, 2, 3, 4, 5
-- RANK: 1, 2, 2, 4, 5
-- DENSE_RANK: 1, 2, 2, 3, 4

Top N Per Group:

-- Get top 3 products per category
WITH RankedProducts AS (
    SELECT 
        CategoryId,
        ProductName,
        Sales,
        ROW_NUMBER() OVER (
            PARTITION BY CategoryId 
            ORDER BY Sales DESC
        ) AS Rank
    FROM Products
)
SELECT 
    CategoryId,
    ProductName,
    Sales
FROM RankedProducts
WHERE Rank <= 3
ORDER BY CategoryId, Rank;

-- Each category gets its top 3 products

Moving Average (Sliding Window):

-- 7-day moving average of sales
SELECT 
    OrderDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7Day
FROM Orders;

-- Explanation:
-- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
-- = Current row + 6 rows before = 7 total rows
-- Calculates average of those 7 rows

-- 30-day moving average
SELECT 
    OrderDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS MovingAvg30Day
FROM Orders;

Lead and Lag (Access Previous/Next Row):

-- Compare current month to previous month
SELECT 
    Month,
    Revenue,
    LAG(Revenue) OVER (ORDER BY Month) AS PreviousMonthRevenue,
    Revenue - LAG(Revenue) OVER (ORDER BY Month) AS MonthOverMonthChange
FROM MonthlySales;

-- Get next row value
SELECT 
    OrderDate,
    Status,
    LEAD(Status) OVER (ORDER BY OrderDate) AS NextStatus
FROM Orders;

-- Access row 2 positions ahead
SELECT 
    Month,
    Revenue,
    LEAD(Revenue, 2) OVER (ORDER BY Month) AS TwoMonthsAhead
FROM MonthlySales;

First and Last Values:

-- First order per customer
SELECT 
    CustomerId,
    OrderDate,
    Amount,
    FIRST_VALUE(OrderDate) OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate
    ) AS FirstOrderDate
FROM Orders;

-- Last order per customer
SELECT 
    CustomerId,
    OrderDate,
    Amount,
    LAST_VALUE(OrderDate) OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastOrderDate
FROM Orders;

-- Note: LAST_VALUE needs frame specification
-- Otherwise it only sees rows up to current row

Percentage of Total:

-- Each product's percentage of total sales
SELECT 
    ProductName,
    Sales,
    CAST(Sales * 100.0 / SUM(Sales) OVER () AS DECIMAL(5,2)) AS PercentOfTotal
FROM Products;

-- Per category percentage
SELECT 
    CategoryId,
    ProductName,
    Sales,
    CAST(Sales * 100.0 / SUM(Sales) OVER (PARTITION BY CategoryId) AS DECIMAL(5,2)) AS PercentOfCategory
FROM Products;

Performance Tips:

-- ✅ Index columns in ORDER BY and PARTITION BY
CREATE INDEX IX_Orders_Date ON Orders(OrderDate);
CREATE INDEX IX_Orders_Customer_Date ON Orders(CustomerId, OrderDate);

-- ✅ Filter data before window function
SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders
WHERE OrderDate >= '2024-01-01';  -- Filter first

-- ❌ Don't filter window function results unnecessarily
-- Window function must calculate all rows first