Window Functions Without the Mystery: Running Totals, Rankings, and Gap Detection

The Query You Wrote Instead

You needed a running total of daily revenue. Without window functions, the usual approach is a correlated subquery: for every row, sum all rows up to that date. It works on your test data. On a year of records it takes ninety seconds, because it is doing quadratic work.

The window function version is one line, and it reads more clearly than the subquery it replaces. The same is true for "rank customers within each region", "compare each month against the previous one", and "find gaps in a sequence". All of these have awkward self-join solutions that window functions make direct.

Window functions have a reputation for being advanced. They are not, really. There is one concept to understand and the rest follows from it.

The One Concept: A Window Is a View From Each Row

An aggregate function collapses many rows into one. SUM(amount) with GROUP BY region gives you one row per region, and the individual rows are gone.

A window function computes an aggregate without collapsing anything. Every input row remains in the output, and alongside it you get a value calculated over a set of related rows, called that row's window.

That is the whole idea. You keep the detail and gain the summary. Everything else is a matter of specifying which related rows each row should look at, and there are three knobs for that.

Knob 1: PARTITION BY

Divides the rows into independent groups. The window never crosses a partition boundary. Omit it and the whole result set is one partition.

This is the closest analogue to GROUP BY, and the key difference is that it does not reduce the row count. Partitioning by region means each row's calculation considers only rows in its own region.

Knob 2: ORDER BY

Establishes a sequence within each partition. Ranking functions need this in order to have anything to rank by. Aggregate functions behave very differently depending on whether it is present, which is the single most common source of confusion.

Knob 3: The Frame

Specifies which rows around the current row are in the window. This is where the surprising defaults live, and it is worth being explicit rather than relying on them.

• With no ORDER BY, the default frame is the entire partition. SUM gives you the partition total on every row.
• With an ORDER BY, the default frame becomes everything from the start of the partition through the current row. That is why adding an order clause silently turns a total into a running total.
• You can state the frame yourself: a fixed number of preceding and following rows, or a range of values relative to the current row's ordering value.

There is one more subtlety worth knowing early, because it produces genuinely puzzling results: the default frame with ORDER BY operates on peer groups, not individual rows. Rows that tie on the ordering expression are all included together. If you order by date and have multiple rows per date, a running total will jump by the whole day at once rather than row by row. Specifying a row-based frame explicitly avoids this.

Pattern 1: Running Totals and Moving Averages

The cumulative case is the default frame, so it needs no explicit frame clause.

SELECT
  order_date,
  daily_total,
  SUM(daily_total) OVER (ORDER BY order_date) AS running_total,
  AVG(daily_total) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_average
FROM daily_revenue
ORDER BY order_date;

The moving average does need an explicit frame, because the default would average everything up to the current row rather than a trailing window. Note that a row-based frame counts rows, not days: if a date is missing from the data, the seven-row window spans more than seven calendar days. When that matters, a range-based frame over the date column is the correct tool.

To get a running total restarted per region, add a partition. Nothing else changes.

Pattern 2: Ranking and Top-N Per Group

Three ranking functions exist and choosing between them is entirely about how you want ties handled.

The top-N-per-group problem is the classic application. Window functions cannot appear in a WHERE clause, because filtering happens before they are computed, so the ranking must go in a subquery or CTE and be filtered outside it.

WITH ranked AS (
  SELECT
    region,
    customer_name,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY revenue DESC
    ) AS rn
  FROM customer_revenue
)
SELECT region, customer_name, revenue
FROM ranked
WHERE rn <= 3
ORDER BY region, rn;

Attempting to put the window function directly in the WHERE clause is the most common beginner error here, and the resulting message is not always obvious about why.

Pattern 3: Comparing Against Neighbouring Rows

LAG and LEAD read a value from a row before or after the current one, which replaces the self-join people usually reach for.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS previous_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change,
  LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year
FROM monthly_revenue
ORDER BY month;

Two practical notes. The first row has no predecessor, so LAG returns null; supply a default third argument if you would rather have zero. And LAG counts rows, not time periods, so a missing month makes the twelve-row offset point at the wrong month. If the series might have gaps, generate a complete date spine and join to it first.

Gap detection uses the same idea. Comparing each row's identifier or timestamp against the previous one and filtering where the difference exceeds the expected step finds missing sequence values, interrupted sessions, or dropped events, all without a self-join.

Practical Notes

• Name the window when you reuse it. Repeating the same OVER clause three times is noisy and easy to get subtly wrong. A WINDOW clause defines it once and references it by name.
• Evaluation order matters. Window functions are computed after WHERE, GROUP BY, and HAVING, and before ORDER BY. This is why they cannot be filtered in the same query level, and it is worth remembering rather than rediscovering.
• An index on partition and order columns helps. The engine needs rows sorted within partitions; an index in that order lets it skip an explicit sort, which on large result sets is the dominant cost.
• Format before reviewing. Window clauses nest parentheses and grow long. A query with three windows on one line is genuinely hard to check for correctness. Running it through the SQL Utility Tools formatter puts each clause on its own line, which makes a misplaced partition or a missing frame immediately visible.
• Dialect support varies at the edges. The core functions are widely available, but range-based frames, IGNORE NULLS, and named windows are not uniformly supported. Check before relying on them, particularly on older MySQL and SQLite versions.

Frequently Asked Questions

What is the difference between PARTITION BY and GROUP BY?

Both divide rows into groups; only GROUP BY collapses them. With GROUP BY you get one output row per group and lose the detail. With PARTITION BY every input row survives and carries its group's computed value alongside it. Use the latter whenever you need detail and summary in the same result.

Why can I not filter on a window function in WHERE?

Because WHERE is evaluated before window functions are computed, so at that point the value does not exist yet. Wrap the query in a CTE or subquery and filter in the outer level. This is not a limitation to work around so much as a consequence of when each clause runs.

Are window functions faster than the subquery equivalent?

Usually much faster, especially against correlated subqueries. A correlated subquery may re-scan for every row, giving quadratic behaviour, whereas a window function typically requires one sort and one pass. The gap widens sharply with row count, which is why the subquery version often looks fine in testing and fails in production.

Conclusion: Detail and Summary Together

Most reporting requirements that feel awkward in SQL are asking for row-level detail and group-level context in the same result. Aggregates cannot do that, which is why people reach for self-joins and correlated subqueries and end up with queries that are both slow and hard to verify.

Window functions are the direct expression of that requirement. Learn the three knobs, be explicit about frames rather than trusting defaults, and the three patterns here will cover the large majority of what you actually need.