Bits of .NET

Running Total Example

-- Calculate cumulative sales per day
SELECT 
    SaleDate,
    Amount,
    SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales
ORDER BY SaleDate;

-- Result:
-- SaleDate    Amount  RunningTotal
-- 2024-01-01  100     100
-- 2024-01-02  150     250
-- 2024-01-03  200     450

-- ROW_NUMBER: Sequential numbering
SELECT 
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

-- RANK: Ranking with gaps
SELECT 
    Name,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

-- DENSE_RANK: Ranking without gaps
SELECT 
    Product,
    Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM Products;

-- LAG/LEAD: Previous/Next row values
SELECT 
    SaleDate,
    Amount,
    LAG(Amount) OVER (ORDER BY SaleDate) AS PreviousDay,
    LEAD(Amount) OVER (ORDER BY SaleDate) AS NextDay
FROM Sales;

Partition By (Group Within Group)

-- Top 3 salaries per department
SELECT *
FROM (
    SELECT 
        Department,
        Name,
        Salary,
        ROW_NUMBER() OVER (
            PARTITION BY Department 
            ORDER BY Salary DESC
        ) AS DeptRank
    FROM Employees
) ranked
WHERE DeptRank <= 3;

-- Running total per category
SELECT 
    Category,
    Product,
    Sales,
    SUM(Sales) OVER (
        PARTITION BY Category 
        ORDER BY SaleDate
    ) AS CategoryRunningTotal
FROM Products;

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

"Replaced 5 self-joins with one window function query. Execution time: 8 seconds → 0.3 seconds. Dashboard analytics became real-time. Window functions are SQL superpowers."