Bits of .NET

Calculating running totals or row rankings with self-joins is complex. Window functions do it elegantly.

Running Total:

SELECT 
    Date,
    Amount,
    SUM(Amount) OVER (ORDER BY Date) AS RunningTotal
FROM Sales
ORDER BY Date;

-- Output:
-- 2024-01-01  100    100
-- 2024-01-02  150    250
-- 2024-01-03  200    450

Rank Within Groups:

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

-- Shows salary rank within each department
-- Reset rank for each new department

Previous/Next Row Values:

SELECT 
    Date,
    Price,
    LAG(Price) OVER (ORDER BY Date) AS PreviousPrice,
    LEAD(Price) OVER (ORDER BY Date) AS NextPrice,
    Price - LAG(Price) OVER (ORDER BY Date) AS PriceChange
FROM StockPrices;

Moving Average:

SELECT 
    Date,
    Sales,
    AVG(Sales) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WeeklyAvg
FROM DailySales;
-- 7-day moving average