SQL window functions: rankings, running totals and comparisons

With GROUP BY you get totals and averages, but you lose the individual rows: ten sales become a single row with the sum. Often, though, you need both at once, for example each sale together with the month's running total or the salesperson's ranking. Window functions do exactly this: they compute a value over a group of rows related to the current row, without reducing the number of rows in the result.

They are available in all the major relational databases, including PostgreSQL, MySQL from version 8, SQL Server, Oracle and SQLite.

The sample table

We will use a sales table with these columns: date, seller, amount.

The basic syntax

A window function is recognisable by its OVER clause:

SELECT date, seller, amount,
       SUM(amount) OVER () AS grand_total
FROM sales;

Each row stays where it is and gains an extra grand_total column, worth 1150 for all of them. The empty brackets after OVER mean the window is the whole table.

PARTITION BY: separate groups

SELECT date, seller, amount,
       SUM(amount) OVER (PARTITION BY seller) AS seller_total
FROM sales;

Now the sum is calculated separately for each seller: Elena's rows show 600, Luca's 550. It is similar to GROUP BY, but the detail remains visible. With this value you can immediately work out each sale's share of the seller's total by dividing amount by seller_total.

ORDER BY: running totals

Adding an ordering inside OVER changes the meaning of the window: it runs from the start of the partition up to the current row.

SELECT date, seller, amount,
       SUM(amount) OVER (PARTITION BY seller ORDER BY date) AS running
FROM sales;

This is the standard way to calculate balances and cumulative totals.

Rankings: ROW_NUMBER, RANK and DENSE_RANK

Three functions assign a position to rows, and they differ only in how they handle ties. If two rows have the same value:

• ROW_NUMBER() still assigns different numbers: 1, 2, 3, 4;
• RANK() gives the same position and skips the next: 1, 2, 2, 4;
• DENSE_RANK() gives the same position without skipping: 1, 2, 2, 3.

A very practical use of ROW_NUMBER is finding the most recent record in each group, for example each seller's latest sale:

SELECT *
FROM (
  SELECT s.*,
         ROW_NUMBER() OVER (PARTITION BY seller ORDER BY date DESC) AS n
  FROM sales s
) t
WHERE n = 1;

Window functions cannot be used directly in WHERE, because they are computed after filtering: that is why the outer subquery is needed. The same pattern is also useful for finding and removing duplicates, keeping only the row with n = 1.

Comparing with neighbouring rows: LAG and LEAD

LAG returns the value from a previous row in the window, LEAD from a following one. They are used to calculate changes:

SELECT date, amount,
       LAG(amount) OVER (ORDER BY date) AS previous,
       amount - LAG(amount) OVER (ORDER BY date) AS change
FROM sales;

On the first row LAG returns NULL, because there is no previous row; you can supply a default value as the third argument. With monthly data, LAG lets you calculate the change from the previous month in a single query, without joining the table to itself.

Moving averages and window frames

You can define precisely which rows to include with the ROWS BETWEEN clause. A three-day moving average:

SELECT date, amount,
       AVG(amount) OVER (
         ORDER BY date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS avg_3
FROM sales;

Each row receives the average of its own amount and the two before it. This is useful for smoothing data with large day-to-day swings.

One subtlety: when there is an ORDER BY with no explicit frame, many databases default to a RANGE frame, which also includes following rows with the same ordering value. If two sales share the same date, the running total adds them together. Specifying ROWS makes the behaviour predictable.

Other useful functions

• NTILE(4) splits the rows into four groups of similar size, for example quartiles of customers by revenue.
• FIRST_VALUE and LAST_VALUE return the first and last value in the window.
• PERCENT_RANK gives a row's relative position, from 0 to 1.

In short

• Window functions compute values over groups of rows without shrinking the result.
• PARTITION BY creates separate groups; ORDER BY makes calculations cumulative.
• ROW_NUMBER, RANK and DENSE_RANK differ only on ties; ROW_NUMBER finds the latest record per group.
• LAG and LEAD compare each row with its neighbours.
• The ROWS BETWEEN clause defines precise frames, such as moving averages.