Checking the Total Row by Row

Concepts covered: Row Offsets, Common Table Expressions, Execution Order, Aggregate Window Fns

"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.

4 checks, in the order to run them

• Row count: the output has 5 rows because the input has 5. A different count means a filter or a join changed the grain
• First row: its balance equals its own amount, 500, because nothing precedes it
• Last row: its balance equals SUM(amount) over the whole table, 250
• Every step: each balance minus the one before equals that row's amount, which catches a missing ORDER BY

WITH balances AS (
  SELECT txn_date, amount,
    SUM(amount) OVER (
      ORDER BY txn_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
  FROM transactions
),
steps AS (
  SELECT txn_date, amount, running_balance,
    running_balance
      - LAG(running_balance, 1, 0) OVER (ORDER BY txn_date) AS step
  FROM balances
)
SELECT txn_date, amount, running_balance, step
FROM steps
WHERE step <> amount;

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 the filter sits

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

-- 2024-03-03 | -120 | -120
-- 2024-03-10 |  -80 | -200
-- 2024-03-14 | -300 | -500

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.

Do

• Say the grain first: 1 row out per row in, each carrying the sum of the rows at or before it
• Type the ordering and an explicit ROWS frame together with OVER, ending the ordering on a unique column
• Name the alias after the meaning: running_balance, balance_so_far, grand_total
• Check the last row against the plain SUM before the interviewer asks
• Compute the running total over all rows in a CTE, then filter the result

Avoid

• Open with GROUP BY when the question names a moment: after, so far, as of
• Leave the frame to the default, or order by date alone when 2 rows can share a date
• Put a WHERE on the same query as the window when the filtered rows belong in the sum
• Call a column total when it changes on every row
• Present 5 numbers without saying how you know they are right

Say the check before you are asked. "The last row is 250, which matches the plain sum, and each step matches its amount, so the ordering is right." That sentence closes the question, and in a screen it is usually where the conversation turns from whether you can write SQL to what else you can be asked.

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.