SQL Window Functions: Running Totals, Rankings and Moving Averages

Sooner or later a report asks for something GROUP BY can't do. Show each sale next to its region's total. Rank sales reps within each region. Show this month's revenue next to last month's. With GROUP BY you get one row per group and lose the details; people often fall back to self-joins, subqueries or a loop in application code.

Window functions solve all of these in a single query. They compute values across a set of related rows (the window) without collapsing them: every input row is still in the output, with the calculated value added as a new column.

They work in PostgreSQL, MySQL 8+, SQL Server, Oracle and SQLite 3.25+. The examples below run as-is in SQLite. If JOINs still feel shaky, start with SQL JOINs Explained with Simple Examples.

Our example data#

CREATE TABLE sales (rep TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('Ana', 'North', 900), ('Ben', 'North', 700), ('Cleo', 'North', 700), ('Dev', 'North', 400),
  ('Eve', 'South', 1200), ('Finn', 'South', 650), ('Gus', 'South', 300);

CREATE TABLE monthly (month TEXT, revenue INTEGER);
INSERT INTO monthly VALUES
  ('2026-01', 12000), ('2026-02', 15000), ('2026-03', 11000),
  ('2026-04', 18000), ('2026-05', 21000), ('2026-06', 19500);

GROUP BY vs a window#

Here's the total per region with GROUP BY. Seven rows go in, two come out:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

Now the same SUM, but followed by OVER, which turns it into a window function:

SELECT rep, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
ORDER BY region, amount DESC;

All seven rows survive. PARTITION BY region splits the rows into groups, like GROUP BY does, but instead of collapsing each group into one row, it gives every row access to its whole group. With an empty OVER (), the window is the entire result.

The anatomy of OVER#

function(...) OVER (
    PARTITION BY ...   -- which rows belong together (optional)
    ORDER BY ...       -- the order of rows inside each partition (optional)
    ROWS BETWEEN ...   -- which of those rows to use for this row (optional)
)

Every part is optional, and each one changes the result. We'll go through them one at a time.

Ranking: ROW_NUMBER, RANK and DENSE_RANK#

Ranking functions need an ORDER BY inside the window. They differ only in how they treat ties, and our data has one: Ben and Cleo both sold 700.

SELECT rep, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales
ORDER BY amount DESC, rep;

• ROW_NUMBER always gives 1, 2, 3, 4… with no ties. Which of Ben and Cleo gets 3 is up to the database, unless you add a tiebreaker such as ORDER BY amount DESC, rep.
• RANK gives ties the same number, then skips: 3, 3, 5. Like a race where two runners share bronze.
• DENSE_RANK gives ties the same number and doesn't skip: 3, 3, 4.

Top N per group#

"The two best reps in each region" is one of the most common reporting questions, and window functions make it easy. But you can't filter on a window function directly:

SELECT rep FROM sales WHERE ROW_NUMBER() OVER (ORDER BY amount) <= 2;
-- Error: misuse of window function ROW_NUMBER()

Window functions are calculated after WHERE, GROUP BY and HAVING, just before the final ORDER BY and LIMIT. So you compute the rank in a subquery or CTE, then filter the result:

WITH ranked AS (
  SELECT rep, region, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) AS pos
  FROM sales
)
SELECT region, pos, rep, amount
FROM ranked
WHERE pos <= 2
ORDER BY region, pos;

Use RANK() instead of ROW_NUMBER() if tied reps should both make the list.

Running totals#

Add ORDER BY to an aggregate's window and it becomes cumulative: each row sums everything from the start of the partition up to itself.

SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly;

Add PARTITION BY to restart the running total for each group, for example a year-to-date total that resets every January.

Comparing with the previous row: LAG and LEAD#

LAG(column) returns the value from the previous row in the window, and LEAD(column) from the next one. Month-over-month change becomes a simple subtraction:

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

The first month has no previous row, so LAG returns NULL. LAG(revenue, 1, 0) would return 0 instead, and LAG(revenue, 12) reaches back twelve rows, handy for year-over-year comparisons.

Window frames: moving averages#

The frame picks which rows of the partition count for the current row. A three-month moving average uses the current row and the two before it:

SELECT month, revenue,
       ROUND(AVG(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_3m
FROM monthly;

The first two months average over fewer rows because there's nothing before them. Other useful frames are ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (a centred window) and ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (the whole partition).

The same default explains a classic puzzle: LAST_VALUE(revenue) OVER (ORDER BY month) returns the current row's value, not the last month's, because the default frame ends at the current row. Give it the whole partition with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Windows over grouped results#

Window functions run after GROUP BY, so they can work on the groups themselves. Here, each region's share of the grand total: the inner SUM(amount) is the region total, and the outer SUM(...) OVER () adds up those totals across all regions:

SELECT region,
       SUM(amount) AS total,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_all
FROM sales
GROUP BY region;

Cheat sheet#

Recap#

• A window function is a normal function followed by OVER (...). It calculates across related rows but keeps every row.
• PARTITION BY splits rows into groups, ORDER BY orders them inside each group, and the frame (ROWS BETWEEN ...) picks the rows used for each calculation.
• ROW_NUMBER, RANK and DENSE_RANK differ only in how they handle ties.
• Window functions run after WHERE and GROUP BY, so filter on them in a CTE or subquery.
• Watch the default frame: use ROWS for strict running totals, and give LAST_VALUE the whole partition.

Once your queries summarise millions of rows, speed starts to matter; SQL Indexes and Query Plans is the natural next step.