SQL Window Functions for Financial Reporting

GROUP BY collapses rows down to one per group — useful, but it throws away the row-level detail in the process. Window functions solve a different, very common finance problem: you want a running total, a rank, or a comparison to the previous period, but you still need every individual row in the result. That's what the OVER clause does, and it's arguably the single most useful thing to learn after basic SQL.

What makes a window function different

A window function calculates across a set of related rows — the "window" — without collapsing them into one row per group the way GROUP BY does. SUM(amount) OVER (ORDER BY invoice_date) gives every row its own running total up to that point, while still returning one row per invoice. Nothing about the underlying data disappears; you just get an extra calculated column alongside it.

Running totals

SELECT invoice_date, amount, SUM(amount) OVER (ORDER BY invoice_date) AS running_total FROM invoices; produces a cumulative total that grows with each row, ordered by date. This is the SQL equivalent of a running-balance column in a spreadsheet, except it recalculates correctly no matter how the underlying data changes — no dragging a formula down and hoping the range updated right.

Ranking rows

Three ranking functions cover most needs, and the difference between them matters. ROW_NUMBER() gives every row a unique number, even if two rows tie. RANK() gives tied rows the same rank but then skips the next number (1, 2, 2, 4). DENSE_RANK() also ties them equally but doesn't skip (1, 2, 2, 3). SELECT customer_name, amount, RANK() OVER (ORDER BY amount DESC) AS spend_rank FROM invoices; ranks customers by spend — useful for "top 10 customers by revenue" questions where you want to see the whole ranked list, not just a filtered top slice.

Comparing to the previous period with LAG and LEAD

This is the function that turns month-over-month variance from a manual VLOOKUP-and-subtract exercise into a single column. LAG(revenue) OVER (ORDER BY month) pulls the previous row's value into the current row, so revenue - LAG(revenue) OVER (ORDER BY month) gives you the change versus the prior period directly, on every row, in one pass. LEAD() does the same thing looking forward instead of back. Both eliminate the self-join that this kind of comparison used to require.

PARTITION BY: resetting the window per group

Most real finance reporting needs this alongside ORDER BY. PARTITION BY restarts the window calculation for each group, so a running total resets at the start of each department or account rather than accumulating across all of them combined. SUM(amount) OVER (PARTITION BY department ORDER BY invoice_date) gives each department its own independent running total within the same single query — no need to write separate queries per department or stitch results back together afterward.

A worked example: monthly revenue with prior-month comparison

SELECT month, SUM(revenue) AS monthly_revenue,
SUM(SUM(revenue)) OVER (ORDER BY month) AS running_total,
SUM(revenue) - LAG(SUM(revenue)) OVER (ORDER BY month) AS change_vs_prior_month
FROM sales GROUP BY month ORDER BY month;

This combines GROUP BY (to get one row per month) with window functions (to add a running total and a prior-period comparison on top) in a single query — the two techniques aren't mutually exclusive, and combining them is normal in real finance reporting.

When to reach for a window function instead of GROUP BY

The test is simple: if the output needs to stay at row-level detail — one row per invoice, per transaction, per day — but you also want a calculation that depends on other rows (a running total, a rank, a comparison to another row), that's a window function. If you genuinely want to collapse many rows into one summary row per group, that's GROUP BY. Needing both at once, as in the worked example above, is common rather than unusual.

A performance note worth knowing early

Window functions are computed after WHERE and GROUP BY have already run, which is why they can reference a grouped total without needing a subquery. It also means they're evaluated on the full result set the rest of the query produces — filter down to the rows you actually need with WHERE first, rather than window-functioning across an entire unfiltered table and only limiting the output afterward. On a small finance dataset this rarely matters; on a table with millions of transaction rows, filtering first is the difference between a query that returns in a second and one that times out.

Building on this

Window functions assume you're already comfortable filtering and grouping data — if WHERE and GROUP BY still feel shaky, it's worth solidifying those first in writing your first SQL query before adding window functions on top. They pair naturally with month-end reconciliation work too, since matching and comparing period-over-period figures is exactly what LAG and running totals are built for.

Window functions assume the basics are in place, which are covered in SQL for accountants and how the output feeds a finance dashboard. Our Tech and Tools CPD for accountants turns the same material into CPD-recognised hours. Also worth reading: data analytics for accountants and advanced Excel skills for accountants.