Running Totals: Beginner

SUM(amount) with GROUP BY account_id collapses the statement to 1 row holding 250, the balance today. A running balance keeps all 5 transaction rows and attaches to each one the sum of every amount dated at or before it. The output has as many rows as the input, and each value depends on the rows above it in date order. So the row count does not change, and the order decides the arithmetic. Say both out loud, because a candidate who writes a correct GROUP BY has answered a different question, and the interviewer scores it as not having listened.

Open with the shape. "This is a running total, so 5 rows come back, and each one shows its own amount plus every amount dated before it." That sentence commits you to the right grain and names the column being added and the order it accumulates along. It also tells the interviewer you have met the pattern before. From there the query is 4 lines, and a working version should be on the screen within a minute or 2. Candidates who type GROUP BY first and only rewrite after 1 row comes back lose 3 or 4 minutes of a 25-minute screen, and the follow-ups about the frame and about the grand total, where the grade is decided, arrive with no time left to answer them well.

Interviewers pick this question because the table fits on 1 screen, a correct answer is short, and the follow-ups give the rest of the slot somewhere to go: which rows the sum sees on a given row, what changes if the ORDER BY is removed, how you would prove the 5 numbers.

The pull toward GROUP BY comes from the word total, since most earlier SQL practice used total to mean collapse the rows. Listen instead for the word that names a moment: after, so far, as of, to date, since launch. A moment is a row, and a total taken at a row is a running total. The signature reads the same on a transactions table as on a donations table or an inventory ledger, which is why the capstones at the end of this lesson arrive in 4 different vocabularies.

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.

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.

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.

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.

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.

The frame is the set of rows the SUM adds together to produce the value on 1 output row, and the engine assembles a fresh one for every row it computes. On the March 7 row the frame holds 3 rows, March 7 and the 2 dated before it, so the value is 500 minus 120 plus 250, which is 630. On the March 10 row the frame has grown by 1 row and the value is 550. Nothing else about the query changes from row to row: the column and the sort stay fixed while the frame gets 1 row longer each time. A candidate who can list the rows in the frame for any row the interviewer points at has understood running totals, and the rest is spelling.

PRECEDING and FOLLOWING measure from the current row outward, and UNBOUNDED means as far as the sorted data reaches in that direction. A frame of ROWS BETWEEN 1 PRECEDING AND CURRENT ROW would hold only the current row and the one before it. That column is a 2-row moving sum, 380 on March 3 and then 130 on March 7, which answers a different question. For a balance after each transaction the start edge sits at the first row no matter how far down the table you are, so UNBOUNDED PRECEDING is the only correct start.

When ORDER BY is present and no frame is written, the SQL standard supplies one: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE gathers rows by the value of the ordering column, so every row carrying the current row's date is inside the frame at once. On this statement no 2 rows share a date, so the RANGE column and the ROWS column read identically, 500 through 250. The day the table gains 2 transactions on 1 date they disagree, and that disagreement gets a section of its own in the intermediate lesson. Writing ROWS out stops tied rows sharing 1 value, but it does not decide which tied row comes first, so end the ordering on a unique column: ORDER BY txn_date, txn_id. Saying "ROWS, written explicitly, with txn_id as the tiebreaker" tells the interviewer you know the default exists and that a sort on dates alone is not unique.

"Which rows does the sum see on the fourth row?" Answer with the rows and the arithmetic: all 4 from March 1 through March 10, so 500 minus 120 plus 250 minus 80, which is 550. "Why UNBOUNDED?" Because the balance after any transaction depends on every earlier one back to the account's opening, so the frame can never start later than row 1. "Why end at CURRENT ROW?" Because the question says after, and after includes the transaction itself; an end edge of 1 PRECEDING would give the balance before each transaction, NULL on the first row since its frame is empty, then 500, 380, 630, 550, which is a legitimate column and a different question. Every one of these answers names specific rows.

These 2 requests sound alike and want different columns. "Show each transaction with the balance after it" wants a number that changes down the page. "Show each transaction with the month's net change beside it" wants the same number, 250, on all 5 rows, so a reader can see how large each movement is against the month. Both are SUM(amount) OVER, both keep 5 rows, and the only difference in the SQL is whether ORDER BY txn_date appears inside the OVER. With it the frame grows row by row; without it the frame is the whole table every time, and the whole table summed is the grand total.

Watch the 2 columns fill. grand_total reads 250 on every row because its window has no ordering and so no notion of earlier, and the frame it gathers is all 5 rows each time. balance_so_far reads 500, 380, 630, 550, 250, growing with each row's frame. The 2 columns meet on the last row, where the total so far has caught up with everything, and that meeting point is a property you can state in the room: the final value of a running total always equals the grand total of the same rows.

Before window functions the grand total beside each row came from a scalar subquery or a join back to a GROUP BY result, and both still work. The subquery runs once and its single value is copied onto each row; the join needs a grouping key and loses rows if any key fails to match. SUM(amount) OVER () does the same work in 1 pass with no second scan and no join condition to get wrong, and it sits in the same SELECT as the growing column, as the earlier code shows. In the room, write the window form and offer the subquery form if asked for an alternative; naming both shows you know what the window replaced.

The follow-up that usually comes next divides the growing column by the constant one to get the share of the month reached after each row, and the intermediate lesson opens with it; everything that query needs is already on the row.

"How do you know that is right?" arrives the moment the 5 balances appear, and the answer is a check you perform out loud on the rows in front of you. Start at 0. Add 500: 500, matches row 1. Subtract 120: 380, matches row 2. Add 250: 630, matches. Subtract 80: 550, matches. Subtract 300: 250, matches. That 250 is also what SUM(amount) returns for the whole table. The 5 rows take 30 seconds, and the interviewer hears you verify the numbers instead of hoping. On a real statement with thousands of rows the same check becomes a query, and both forms are worth knowing.

The step check is the strongest of the 4 because it fails on the most common mistake. Remove ORDER BY txn_date from the window and every row reads 250; the first step is then 250 against an amount of 500, and the check query returns 5 rows instead of 0. It also fails when the sort key is wrong: order the window by txn_id descending and the balances come out as 250, negative 250, negative 130, negative 380, negative 300 read in date order, and the steps disagree with the amounts on every row. A join that fans out fails 2 checks at once: the output has more than 5 rows, and the last row moves away from the closing 250 in the direction of the duplicated amounts, up for a doubled deposit and down for a doubled withdrawal. The row-count check also catches a filter that removed a transaction.

WHERE runs before the window, so a row the filter removes never enters anyone's frame. Asked for the balance after each withdrawal, a candidate who filters to amount < 0 gets negative 120 on the first withdrawal and negative 200 on the second, then negative 500 on the last, because the deposits that fund the account were removed before the sum ran. The true balances after those withdrawals are 380 and 550, then 250 after the last one. The fix is a CTE that computes the running total over every row; filter its result afterward. The row-count check and the last-row check both catch the wrong version: 3 rows instead of 5, and a closing figure of negative 500 against a statement that closes at 250.