If you’ve ever tried to calculate a running total, rank products by sales, or smooth out noisy data with a moving average, you’ve probably reached for Excel or Python — but SQL window functions can do all of this directly in the database, often faster and with less code. For beginner data analysts, Excel users, and aspiring SQL programmers, SQL window functions are one of the highest-leverage skills you can learn after mastering the basics of SELECT, WHERE, and GROUP BY.

A window function performs a calculation across a set of rows — a “window” — that are related to the current row, without collapsing those rows into a single result the way GROUP BY does. That means you can calculate a running total, a rank, or a moving average while still seeing every individual row of detail.

In this guide, you’ll learn how SQL window functions work, how to calculate running totals, rankings, and moving averages step by step, and how to avoid the most common beginner mistakes — with clear, practical examples you can adapt to your own data.

What Is a Window Function?

A window function uses the OVER() clause to define a “window” of rows the calculation should consider. Unlike GROUP BY, which reduces multiple rows into one summary row, a window function keeps every original row while adding a calculated column alongside it.

The basic syntax looks like this:

SELECT
    column1,
    column2,
    SOME_FUNCTION() OVER (
        PARTITION BY grouping_column
        ORDER BY sorting_column
    ) AS result
FROM table_name;
  • PARTITION BY — optional; divides rows into groups, similar to GROUP BY, but without collapsing them.
  • ORDER BY — controls the order rows are processed in within each partition, which matters for running totals and rankings.

Throughout this guide, we’ll use a Sales table with columns: SaleDate, Region, Amount.

Calculating Running Totals

A running total (or cumulative sum) adds up values as you move through the rows in order. Use SUM() as a window function with ORDER BY:

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

Each row’s RunningTotal includes its own Amount plus every prior row’s Amount, based on the ORDER BY SaleDate.

Running Totals Within Groups

Add PARTITION BY to calculate a separate running total for each group — for example, a running total per region instead of one running total for the entire table:

SELECT
    Region,
    SaleDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY Region
        ORDER BY SaleDate
    ) AS RunningTotalByRegion
FROM Sales;

Now the running total resets for each region, instead of accumulating across all of them together.

Ranking Rows with SQL Window Functions

SQL provides three closely related ranking functions:

  1. ROW_NUMBER() — assigns a unique, sequential number to each row, even if values tie.
  2. RANK() — assigns the same rank to tied values, then skips the next rank number (1, 2, 2, 4).
  3. DENSE_RANK() — assigns the same rank to tied values, without skipping the next number (1, 2, 2, 3).
SELECT
    Region,
    Amount,
    ROW_NUMBER() OVER (ORDER BY Amount DESC) AS RowNum,
    RANK() OVER (ORDER BY Amount DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Amount DESC) AS DenseRank
FROM Sales;

Ranking Within Groups

Just like running totals, ranking becomes far more useful when combined with PARTITION BY — for example, ranking sales within each region separately:

SELECT
    Region,
    Amount,
    RANK() OVER (
        PARTITION BY Region
        ORDER BY Amount DESC
    ) AS RegionRank
FROM Sales;

This is a common technique for questions like “what was each region’s top-selling day?” — you’d simply filter the result to WHERE RegionRank = 1.

Calculating Moving Averages

A moving average smooths out short-term fluctuations by averaging a fixed number of surrounding rows. SQL window functions handle this with a frame clause, using ROWS BETWEEN:

SELECT
    SaleDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS SevenDayMovingAverage
FROM Sales;

This calculates the average of the current row plus the 6 rows before it — a simple 7-day moving average when your data has one row per day.

Common Frame Clause Patterns

  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — a trailing average (e.g., last 7 days including today).
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — equivalent to a running total or running average from the very first row.
  • ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING — a centered average, useful for smoothing trends symmetrically.

Common Mistakes to Avoid

  • Forgetting ORDER BY inside OVER() for running totals or rankings — without it, the calculation order is unpredictable.
  • Confusing RANK() and DENSE_RANK(), especially when ties matter for downstream filtering.
  • Using PARTITION BY when you meant GROUP BY, or vice versa — remember, window functions keep every row, while GROUP BY collapses them.
  • Filtering directly on a window function in WHERE. Window functions can’t be used in WHERE; wrap the query in a subquery or CTE and filter in the outer query instead.
-- This will NOT work:
SELECT * FROM Sales WHERE RANK() OVER (ORDER BY Amount DESC) = 1;

-- Do this instead:
WITH RankedSales AS (
    SELECT *, RANK() OVER (ORDER BY Amount DESC) AS Rank
    FROM Sales
)
SELECT * FROM RankedSales WHERE Rank = 1;

Conclusion

SQL window functions give you the power to calculate running totals, rankings, and moving averages without ever leaving the database — no need to export data to Excel or write separate Python scripts. The OVER() clause, combined with PARTITION BY and ORDER BY, lets you keep every row of detail while still layering in powerful, row-by-row calculations.

To recap: use SUM() OVER (ORDER BY ...) for running totals, ROW_NUMBER(), RANK(), or DENSE_RANK() for rankings, and AVG() OVER (... ROWS BETWEEN ...) for moving averages — and reach for PARTITION BY whenever you need these calculations to reset per group instead of running across your entire table.

Once SQL window functions click, they become one of the most practical tools in your SQL toolkit, turning multi-step spreadsheet workflows into a single, readable query you can run directly against your live data.