SUM OVER (ORDER BY ...) Basics

Concepts covered: Aggregate Window Fns, Sorting Results, Window Frames, Self-Join

Start from the aggregate you would type without thinking. SUM(amount) returns 1 row, 250. Append OVER () and the same 250 appears on each of the 5 rows, because a window with nothing inside the parentheses spans the whole table. Put ORDER BY txn_date inside them and the value begins to grow: 500 on March 1, 380 on March 3, 630 on March 7. The ordering is the entire change. It tells the engine which rows count as earlier than the row being computed, and a sum taken over the earlier rows and this one is a total so far.

-- Balance after each transaction, accumulated in date order
SELECT
  txn_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    ORDER BY txn_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM transactions
ORDER BY txn_date;

SUM(amount) is the quantity being added up. OVER turns the aggregate into a per-row calculation that keeps every input row instead of collapsing them. ORDER BY txn_date sorts the rows inside the window so the engine knows which ones precede the current one. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW states the span of rows added for each row, from the first through this one, and the next section takes it apart word by word. AS running_balance names the column after what the number means, which is part of the answer: a column called sum tells the dashboard reader nothing.

SELECT txn_id, txn_date, amount,
  SUM(amount) OVER (
    ORDER BY txn_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM transactions

The rows are stored out of date order, with transaction 3 first and transaction 5 third, and the first thing the engine does is sort them by txn_date. Then it walks down the sorted list: 500, then 500 plus negative 120 gives 380, then plus 250 gives 630, then minus 80 gives 550, then minus 300 gives 250. The last value matches the plain SUM(amount), as it must, since the last row's span is every row in the table. Narrate it in those words while the interviewer watches: the engine sorts before it accumulates. The first row's balance is its own amount because nothing precedes it.

Each ORDER BY has its own job

The query has 2 ORDER BY clauses, and they do different work. The one inside OVER decides the arithmetic: which rows are earlier, and therefore which amounts each sum includes. The one at the end decides display order only. Delete the last line and every running_balance value stays the same, but the rows can come back in whatever order the engine found convenient, which on a dashboard looks like a broken running total even though every number is right. "What happens if I remove the final ORDER BY" is a common probe, and the complete answer is: the values do not change, the row order becomes undefined, so I keep it for the reader's sake.

The query the window replaces

-- The pre-window way: every row joined to every row at or before it
SELECT t.txn_date, t.amount, SUM(e.amount) AS running_balance
FROM transactions t
JOIN transactions e ON e.txn_date <= t.txn_date
GROUP BY t.txn_id, t.txn_date, t.amount
ORDER BY t.txn_date;

"Could you do this without window functions" is the follow-up for a candidate who typed the window too quickly, and the self-join is the answer. Each row joins to every row dated at or before it and sums the matches. However the engine finds those matches, it has to produce and add every one of them: 15 pairs for 5 rows, about 500,000 for a 1,000-row statement, growing with the square of the row count. The window produces the same 5 balances from 1 sort and 1 pass. Naming the cost is what the interviewer wants to hear, because it shows you know why the window exists rather than only how to spell it.

• "Remove ORDER BY from inside OVER?" Every row reads 250, the whole-table sum, since nothing marks rows as earlier
• "What if I remove the ORDER BY at the end?" Values unchanged, display order undefined
• "Can I use COUNT or AVG?" Same window: COUNT(*) in place of SUM(amount) gives 1, 2, 3, 4, 5
• "Does the window run before or after WHERE?" After: a filtered row never enters any sum, as the last section shows

SUM ... OVER (ORDER BY ...) with these frame keywords runs unchanged on PostgreSQL, MySQL 8, SQL Server, BigQuery, Snowflake and Spark SQL, which covers every major engine, so the query you practise here is the one you will type in the room.

About this lesson

This section is part of the Running Totals: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.