SQL Server Window Functions: Ranking, LAG and Running Totals

One of the most common needs in reporting queries is to add calculations next to each row without losing the rows themselves: a row number, last month’s value, a running total, a moving average. GROUP BY collapses rows into one; window functions (anything with an OVER clause) keep every row. Here are the four patterns I use most.

Sample table

CREATE TABLE dbo.MonthlySales (
    Store   varchar(20),
    Month   date,
    Amount  decimal(18,2)
);

INSERT INTO dbo.MonthlySales VALUES
 ('Central', '2026-01-01', 120000), ('Central', '2026-02-01', 135000),
 ('Central', '2026-03-01', 128000), ('Central', '2026-04-01', 150000),
 ('Coast',   '2026-01-01',  80000), ('Coast',   '2026-02-01',  76000),
 ('Coast',   '2026-03-01',  91000), ('Coast',   '2026-04-01',  99000);

1. Ranking: ROW_NUMBER, RANK, DENSE_RANK

To find each store’s best month, number the rows per store and keep the first one:

WITH Ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Store ORDER BY Amount DESC) AS RowNo
    FROM dbo.MonthlySales
)
SELECT Store, Month, Amount
FROM Ranked
WHERE RowNo = 1;

ROW_NUMBER gives ties different numbers. If ties should share a rank, use RANK (leaves gaps: 1, 1, 3) or DENSE_RANK (no gaps: 1, 1, 2). The same pattern is also handy for cleaning up duplicates: delete everything where RowNo > 1.

2. Comparing with the previous period: LAG

SELECT Store, Month, Amount,
       LAG(Amount) OVER (PARTITION BY Store ORDER BY Month) AS PrevMonth,
       Amount - LAG(Amount) OVER (PARTITION BY Store ORDER BY Month) AS Diff
FROM dbo.MonthlySales
ORDER BY Store, Month;

LAG returns the value from the previous row, LEAD from the next one. The second argument tells it how many rows to go back, so LAG(Amount, 12) gives you the same month last year. No self-join needed.

3. Running total

SELECT Store, Month, Amount,
       SUM(Amount) OVER (
           PARTITION BY Store
           ORDER BY Month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RunningTotal
FROM dbo.MonthlySales;

I’d always spell out the ROWS clause. If you leave it out, SQL Server defaults to RANGE. When there are several rows with the same date you can get results you didn’t expect, and performance is usually worse too.

4. Moving average

SELECT Store, Month, Amount,
       AVG(Amount) OVER (
           PARTITION BY Store
           ORDER BY Month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS ThreeMonthAvg
FROM dbo.MonthlySales;

Change the frame and you get a 7-day, 12-month or any other window you need. It’s a common way to smooth out seasonal swings in a report.

Performance notes

• An index that matches your PARTITION BY and ORDER BY columns (for example (Store, Month) INCLUDE (Amount)) cuts the cost of the sort dramatically.
• You can’t use a window function directly in WHERE. Wrap it in a CTE or subquery as shown above.
• Tired of repeating the same OVER definition? From SQL Server 2022 on you can name the window with a WINDOW clause and extend it with a frame where you need one. (Don’t put the frame in the named window itself, because functions like LAG don’t accept one.)

-- SQL Server 2022+
SELECT Store, Month, Amount,
       LAG(Amount) OVER w AS PrevMonth,
       SUM(Amount) OVER (w ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM dbo.MonthlySales
WINDOW w AS (PARTITION BY Store ORDER BY Month);

Common mistakes

• Non-deterministic ordering on ties: If the ORDER BY column has ties, ROW_NUMBER can pick a different “first” row on each run. Add a unique column, such as an ID, as a tiebreaker.
• The LAST_VALUE trap: Without an explicit frame, the default frame ends at the current row, so LAST_VALUE usually returns the row’s own value. Write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• NULL on the first row from LAG: When there is no previous row, the result is NULL. Pass a default as the third argument: LAG(Amount, 1, 0).
• Missing months: LAG returns the previous row, not the previous month. If a month has no sales, comparisons shift; fill the gaps with a date table first.

Frequently asked questions

Window function or GROUP BY?

Use GROUP BY when one summary row per group is enough. Use a window function when you need the detail row and a group-level calculation (rank, share of total, previous value) side by side.

How do I split rows into equal buckets?

NTILE(4) OVER (ORDER BY Amount DESC) splits rows into four equal groups, which is handy for ranking stores or products into quartiles.

Related posts

• Why Your Power BI Model Needs a Date Table (and How to Build One)
• Using INTERSECT and EXCEPT in T-SQL
• Responsible Web Scraping in Python: A Solid Start for Price Monitoring

STAY IN THE LOOP

New guides, sorted by topic in a single email.

Every Monday, the week's new posts arrive grouped by topic. No new posts, no email.

WEEKLY NEWSLETTER

Microsoft FabricPower BIDAXSQL Server & T-SQLPython

E-posta Subscribe

I have read the privacy notice and agree to my email address being processed for the weekly newsletter.

Nothing is sent until you click the link in the confirmation email. Unsubscribe anytime with one click.