Window Functions: Ranking & Running Totals

Calculations that keep every row

A GROUP BY aggregate collapses many rows into one summary row — you lose the individual rows. A window function does something different and powerful: it performs a calculation across a set of related rows but keeps every original row, simply adding the computed value as an extra column. So you can show each order and its rank, or each sale and the running total so far — side by side. This is the signature “beginner-to-expert” feature of modern MySQL 8.

The key piece of syntax is OVER (...). It defines the window — the set of rows the function looks at. Inside OVER you can add PARTITION BY (split rows into groups, like GROUP BY but without collapsing) and ORDER BY (order the rows within each group, which ranking and running totals need).

We will use this sales table throughout:

ROWNUMBER, RANK and DENSERANK

The most common window functions number or rank rows. To rank sales reps within each region from highest amount to lowest, you partition by region and order by amount:

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

Reading the OVER clause: PARTITION BY region restarts the numbering for each region (North’s reps are ranked separately from South’s), and ORDER BY amount DESC ranks the highest amount as number 1. The three functions differ only in how they handle ties (two equal amounts): ROW_NUMBER always gives a unique number even to ties (1, 2, 3); RANK gives ties the same number but then skips the next (1, 1, 3); DENSE_RANK gives ties the same number and does not skip (1, 1, 2).

A real use: top N per group

Window ranking unlocks a question that is awkward without it: “the top 2 sales reps in each region”. You compute the rank in an inner query, then keep only ranks 1 and 2 in the outer query (you cannot filter on a window function directly in WHERE, so you wrap it):

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

The inner query (a derived table, which you met in the subqueries lesson) adds the rn ranking column. The outer query then keeps only rows where rn is 1 or 2 — the top two per region. This “rank then filter” pattern is one of the most common real-world uses of window functions.

Running totals with SUM() OVER

Add ORDER BY inside OVER to an aggregate like SUM and it becomes a running (cumulative) total — each row shows the sum of itself and all rows before it. This is how dashboards draw “total sales to date” lines.

SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

SUM(amount) OVER (ORDER BY id) tells MySQL: for each row, add up amount from the first row up to this row, in id order. Row 1 shows 500; row 2 shows 500 + 900 = 1400; row 3 adds another 500 to reach 1900, and so on. Without ORDER BY inside OVER, SUM(amount) OVER () would instead show the grand total on every row.

LAG and LEAD — compare to the previous or next row

LAG() pulls a value from the previous row and LEAD() from the next row, in the window’s order. This makes row-to-row comparisons — “how much more than the previous sale?” — trivial:

SELECT
  id,
  amount,
  LAG(amount) OVER (ORDER BY id)  AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY id) AS change
FROM sales;

LAG(amount) OVER (ORDER BY id) fetches the amount from the row one position earlier. Subtracting it from the current amount gives the change since the last sale. The very first row has no previous row, so LAG returns NULL there (and the change is NULL too).