Running Totals and Moving Averages in SQL Server

Window FRAME = which rows around the current row are included:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -> running total
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW           -> 3-row moving window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING           -> centered window
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   -> "remaining" total

ROWS = physical rows.

RANGE = rows with the same ORDER BY value are treated as one (ties grouped). DEFAULT frame when ORDER BY is present = RANGE UNBOUNDED PRECEDING (slower, tie surprises!)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: BankTransactions

DROP TABLE IF EXISTS dbo.BankTransactions;

CREATE TABLE dbo.BankTransactions
(
    TxnID     INT IDENTITY(1,1) PRIMARY KEY,
    AccountNo VARCHAR(10)   NOT NULL,
    TxnDate   DATE          NOT NULL,
    TxnType   VARCHAR(10)   NOT NULL CHECK (TxnType IN ('Credit', 'Debit')),
    Amount    DECIMAL(12,2) NOT NULL,
    Remarks   VARCHAR(50)   NULL
);

INSERT INTO dbo.BankTransactions (AccountNo, TxnDate, TxnType, Amount, Remarks) VALUES
('ACC1001', '2025-01-01', 'Credit', 50000, 'Opening deposit'),
('ACC1001', '2025-01-05', 'Debit',   5000, 'ATM'),
('ACC1001', '2025-01-05', 'Debit',   1200, 'Groceries'),          -- same date (tie)
('ACC1001', '2025-01-10', 'Debit',  15000, 'Rent'),
('ACC1001', '2025-01-31', 'Credit', 60000, 'Salary'),
('ACC1001', '2025-02-03', 'Debit',  20000, 'EMI'),
('ACC1001', '2025-02-14', 'Debit',   3500, 'Dinner'),
('ACC1001', '2025-02-28', 'Credit', 60000, 'Salary'),
('ACC2002', '2025-01-02', 'Credit', 20000, 'Opening deposit'),
('ACC2002', '2025-01-15', 'Debit',  12000, 'Laptop EMI'),
('ACC2002', '2025-01-20', 'Debit',   9000, 'Travel'),             -- goes negative!
('ACC2002', '2025-01-31', 'Credit', 35000, 'Salary'),
('ACC2002', '2025-02-10', 'Debit',   4000, 'Shopping'),
('ACC2002', '2025-02-28', 'Credit', 35000, 'Salary');
GO

(14 rows affected)

Part A: Running Totals

Running balance per account (credit = +, debit = -)

SELECT AccountNo, TxnDate, TxnType, Amount,
       SUM(CASE WHEN TxnType = 'Credit' THEN Amount ELSE -Amount END)
           OVER (PARTITION BY AccountNo ORDER BY TxnDate, TxnID
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningBalance
FROM dbo.BankTransactions
ORDER BY AccountNo, TxnDate, TxnID;

Find the moments when an account balance went Negative

WITH b AS
(
    SELECT AccountNo, TxnDate, Remarks,
           SUM(CASE WHEN TxnType = 'Credit' THEN Amount ELSE -Amount END)
               OVER (PARTITION BY AccountNo ORDER BY TxnDate, TxnID ROWS UNBOUNDED PRECEDING) AS Balance
    FROM dbo.BankTransactions
)
SELECT * FROM b WHERE Balance < 0;

Rows vs Range: look at 2025-01-05 (two rows with the same date)

SELECT AccountNo, TxnDate, Amount,
       SUM(Amount) OVER (PARTITION BY AccountNo ORDER BY TxnDate ROWS  UNBOUNDED PRECEDING) AS RunningRows,
       SUM(Amount) OVER (PARTITION BY AccountNo ORDER BY TxnDate RANGE UNBOUNDED PRECEDING) AS RunningRange,
       SUM(Amount) OVER (PARTITION BY AccountNo ORDER BY TxnDate)                           AS RunningDefault  -- = RANGE
FROM dbo.BankTransactions
WHERE AccountNo = 'ACC1001' AND TxnType = 'Debit';

Year-to-date (ytd) and month-to-date (mtd) sales

WITH Daily AS
(
    SELECT SaleDate, SUM(TotalAmount) AS DayRevenue FROM dbo.Sales GROUP BY SaleDate
)
SELECT SaleDate, DayRevenue,
       SUM(DayRevenue) OVER (PARTITION BY YEAR(SaleDate)                  ORDER BY SaleDate ROWS UNBOUNDED PRECEDING) AS YTD,
       SUM(DayRevenue) OVER (PARTITION BY YEAR(SaleDate), MONTH(SaleDate) ORDER BY SaleDate ROWS UNBOUNDED PRECEDING) AS MTD
FROM Daily
ORDER BY SaleDate;

Running count of orders and running customer count per year

SELECT OrderID, OrderDate, CustomerID,
       COUNT(*) OVER (PARTITION BY YEAR(OrderDate) ORDER BY OrderDate, OrderID ROWS UNBOUNDED PRECEDING) AS OrdersSoFarThisYear
FROM dbo.Orders
ORDER BY OrderDate, OrderID;

Part B: Moving Averages

3-day moving average of stock prices

SELECT Symbol, TradeDate, ClosePrice,
       CAST(AVG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS DECIMAL(10,2)) AS MA3,
       CAST(AVG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate
                                  ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS DECIMAL(10,2)) AS MA5,
       COUNT(*)             OVER (PARTITION BY Symbol ORDER BY TradeDate
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RowsInWindow
FROM dbo.StockPrices
ORDER BY Symbol, TradeDate;
-- First rows have fewer than 3 values. Use RowsInWindow to hide incomplete averages if needed.

7-day moving average of daily sales (fill missing days first with a calendar)

WITH Calendar AS
(
    SELECT CAST('2025-01-01' AS DATE) AS d
    UNION ALL SELECT DATEADD(DAY, 1, d) FROM Calendar WHERE d < '2025-06-30'
),
Daily AS
(
    SELECT c.d AS SaleDate, ISNULL(SUM(s.TotalAmount), 0) AS DayRevenue
    FROM Calendar c LEFT JOIN dbo.Sales s ON s.SaleDate = c.d
    GROUP BY c.d
)
SELECT SaleDate, DayRevenue,
       CAST(AVG(DayRevenue) OVER (ORDER BY SaleDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(12,2)) AS MovingAvg7Day,
       SUM(DayRevenue)      OVER (ORDER BY SaleDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)                    AS Rolling7DayTotal
FROM Daily
ORDER BY SaleDate
OPTION (MAXRECURSION 200);

Centered moving average (1 before, current, 1 after)

SELECT Symbol, TradeDate, ClosePrice,
       CAST(AVG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate
                                  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS DECIMAL(10,2)) AS CenteredMA3
FROM dbo.StockPrices
WHERE Symbol = 'INFY';

Rolling 3-month revenue (monthly level)

WITH Monthly AS
(
    SELECT DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS OrderMonth,
           SUM(oi.LineTotal) AS Revenue
    FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    GROUP BY DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1)
)
SELECT OrderMonth, Revenue,
       SUM(Revenue) OVER (ORDER BY OrderMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Rolling3MonthRevenue,
       AVG(Revenue) OVER (ORDER BY OrderMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Rolling3MonthAvg
FROM Monthly
ORDER BY OrderMonth;

Part C: Other Frame Tricks

Rolling MIN / MAX: 5-day high and low

SELECT Symbol, TradeDate, ClosePrice,
       MAX(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS High5Day,
       MIN(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS Low5Day
FROM dbo.StockPrices
ORDER BY Symbol, TradeDate;

Remaining total: how much stock value is still "ahead"

SELECT ProductName, UnitPrice * StockQty AS StockValue,
       SUM(UnitPrice * StockQty) OVER (ORDER BY UnitPrice * StockQty DESC
                                       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RemainingValue
FROM dbo.Products;

Pareto / 80-20 analysis: which products make 80% of revenue?

WITH ProdRev AS
(
    SELECT p.ProductName, SUM(oi.LineTotal) AS Revenue
    FROM dbo.OrderItems oi JOIN dbo.Products p ON p.ProductID = oi.ProductID
    GROUP BY p.ProductName
),
Cume AS
(
    SELECT ProductName, Revenue,
           SUM(Revenue) OVER (ORDER BY Revenue DESC ROWS UNBOUNDED PRECEDING) * 100.0
               / SUM(Revenue) OVER () AS CumulativePct
    FROM ProdRev
)
SELECT ProductName, Revenue, CAST(CumulativePct AS DECIMAL(5,2)) AS CumulativePct,
       CASE WHEN CumulativePct <= 80 THEN 'A (top 80%)' ELSE 'B/C' END AS ParetoClass
FROM Cume
ORDER BY Revenue DESC;

Practice Questions

1. Show running total of 2025 sales per Region, by date.
2. Show a 3-order moving average of order value for each customer.
3. Show each account's month-end balance (hint: running total + last row per month).
4. Find the first date when cumulative 2025 sales crossed 50 lakh (5,000,000).
5. Compute a 7-day rolling count of orders.