"Show Me the Running Balance"

Concepts covered: Aggregate Window Fns

The question comes in the vocabulary of whoever owns the dashboard. A payments team wants the balance after each transaction posts. For a fundraising team it is how much a campaign had raised after every gift, for a warehouse the stock on hand after each receipt and each pick, and for a growth team the number of people signed up as of each day since launch. Every one of these asks for a row plus the sum of the rows before it, and the interviewer is checking whether you hear that shape underneath the nouns before you commit to a query.

The grain is the whole question

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.

The left result answers what account 4471 holds today. The right result answers what it held after each of the 5 transactions, which is what the statement page prints and what the interviewer asked for. The last row on the right carries 250, the same figure as the single row on the left, and that equality is the first check the final section of this lesson runs.

SELECT account_id, SUM(amount) AS balance
FROM transactions
GROUP BY account_id;

-- account_id | balance
-- 4471       | 250

What to say before you type

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.

Opens with GROUP BY
  • Gets 1 row back and has to be told the question wanted 5
  • Rewrites under time pressure and forgets the ORDER BY inside the window
  • Reaches the frame follow-up with 8 minutes left instead of 15
  • Never gets asked how to prove the numbers, which is the part that shows understanding
Names the shape first
  • Says "running total, 1 row per transaction, summed in date order" inside 20 seconds
  • Says which column gets summed in which order before touching the keyboard, frame included
  • Has 5 correct balances on screen inside 2 minutes
  • Spends the saved time on the frame and the grand total, where the score is set

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.

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.