Window functions, the SQL most people skip

Most people learn GROUP BY and stop. So when they need a running total or a rank, they reach for a self-join, a subquery, or worse, they pull the data into Python and loop. Window functions do all of it in one clause, and once you see them you will wonder how you managed without. They are the most useful SQL that most analysts never learn.

The one idea

GROUP BY collapses many rows into one summary row, you lose the detail. A window function computes across a set of rows too, but keeps every row, attaching the computed value alongside. The "window" is which rows each calculation sees, defined by OVER (...). That one distinction, aggregate but keep the rows, is the whole feature.

Set up some data

A small sales table, with SQLite so you can run every query below.

CREATE TABLE sales (region TEXT, month INT, amount INT);
INSERT INTO sales VALUES
  ('East', 1, 100), ('East', 2, 150), ('East', 3, 120),
  ('West', 1, 200), ('West', 2, 180), ('West', 3, 260);

Running total: SUM with an ORDER BY

A regular SUM gives one number. Put OVER (ORDER BY ...) on it and it becomes a running sum: each row gets the total of everything up to and including itself.

SELECT region, month, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM sales;

East|1|100|100
East|2|150|250
East|3|120|370
West|1|200|200
West|2|180|380
West|3|260|640

PARTITION BY region restarts the total for each region (East resets at West), and ORDER BY month defines the accumulation order. Every original row survives, now carrying its running total. Doing this with a self-join would be a page of SQL; here it is one line.

Ranking: who is biggest, per group

RANK() numbers rows within each partition by an ordering. Here, the top-selling month per region.

SELECT region, month, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales;

East|2|150|1
East|3|120|2
East|1|100|3
West|3|260|1
West|1|200|2
West|2|180|3

Within each region, the best month gets rank 1 (West's 260 wins, then 200, then 180). This is how "top 3 per category" queries work, rank inside the partition, then filter to rnk <= 3. No correlated subquery required.

Row-to-row change: LAG

LAG reaches back to the previous row in the window, so you can compute month-over-month change without joining the table to itself.

SELECT region, month, amount,
       amount - LAG(amount) OVER (PARTITION BY region ORDER BY month) AS change
FROM sales;

East|1|100|          (no previous row)
East|2|150|50
East|3|120|-30
West|1|200|
West|2|180|-20
West|3|260|80

Each row now knows how it differs from the month before, with the first month correctly blank because there is nothing before it. LAG and its twin LEAD (which looks forward) turn "compare each row to its neighbor" from a painful join into a single expression.

Why this matters

The pattern behind all three is identical: a function, then OVER (PARTITION BY ... ORDER BY ...) describing the window. Change the function and you change the question, running totals, ranks, moving averages, percentiles, gaps, all follow the same shape. And crucially, the detail rows survive, so you can compute a summary and keep the raw data in one pass, exactly what dashboards and cohort analyses need.

Window functions are the line between writing SQL that fetches rows and writing SQL that analyzes them. Reaching for them instead of dragging data into a loop is a habit the data analysis with SQL track builds early, because it is what separates a query you run once from a query that answers a real business question.