BeginnerSQL · 25 min

Running Totals: Beginner

Here is the most common follow-up to your first window-function question in a SQL interview: 'now compute the running total.' Running totals appear in almost every dashboard, every reporting query, every monthly close. The pattern is small, the SQL is short, and the magic word that earns the credit is 'SUM OVER.' This lesson teaches you to recognize the question in the first ten seconds, write the canonical query from memory, and avoid the one bug that every first-time author of a running total ships at least once.

Spot a running-total question the moment the interviewer says 'cumulative' or 'year-to-date'

Write the canonical SUM OVER from muscle memory

Articulate what 'running' means in a sentence the interviewer can repeat back

Avoid the silent bug that makes a running total return wrong numbers

"Show Me the Running Balance"

Daily Life

Interviews

Recognize that any question asking for cumulative, year-to-date, or rolling aggregates is a running total pattern.

The English of the question always sounds simple. 'For each transaction, show the account's running balance.' 'Show me revenue accumulated since the start of the year, broken down by day.' 'Compute the cumulative number of users who have ever logged in.' Each one is a running total. Each one wants the same SQL shape: a SUM with an OVER clause that tells the engine which rows to add up.

You are being tested on a running total when you hear:

▸"cumulative ... over time"
▸"running balance," "running total," "running count"
▸"year-to-date," "month-to-date," "life-to-date"
▸"7-day trailing," "30-day rolling"
▸"the value as of date X"

What 'running' means, and how to spot it

A running total is not just a sum. A regular SUM with a GROUP BY produces one row per group, with the total for that group. A running total produces one row per input row, with the total of every row that came before plus the current row. The output has the same number of rows as the input. Each row's value depends on the rows above it in some order. That ordering is the part the interviewer is checking you understand.

•Weak opening

Writes SELECT SUM(amount) FROM transactions GROUP BY account_id
Returns one row per account with the lifetime total
Has to be corrected when the interviewer asks for per-row output
Loses time recovering from a misread of the question

✓Strong opening

Says 'This is a running total, I will use SUM OVER'
Names the partition (per account), the order (by date), and the frame
Writes the canonical shape in twelve lines without hesitation
Has working SQL on the page within ninety seconds

Why companies care

Running totals power every financial and product-analytics dashboard. Account balance. Lifetime revenue. Cumulative active users. Year-to-date sales. The pattern is unavoidable. If you cannot write this query, an entire family of business questions is closed to you. That is why it is one of the most-asked SQL screen follow-ups.

SUM OVER (ORDER BY ...) Basics

Daily Life

Interviews

Write a correct running total using SUM() OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

The canonical query has the same shape every time. SUM of the column to total, OVER with three parts: PARTITION BY for the group, ORDER BY for the time column, and a frame clause that says which rows to include. Memorize this shape. Type it without thinking.

The query you should be able to write from memory

	/* Running balance per account, ordered by transaction date */
	SELECT
	account_id,
	txn_date,
	amount,
	SUM(amount) OVER (
	PARTITION BY account_id
	ORDER BY txn_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS running_balance
	FROM transactions
	ORDER BY account_id, txn_date

Read the query out loud

Each clause does a specific job. SUM(amount) is the value you are accumulating. PARTITION BY account_id makes each account its own running total; the balance restarts at every new account. ORDER BY txn_date defines the order of accumulation; rows are added in date order. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the frame; it says 'sum every row from the first row in this partition up to and including this row.' Together, those four pieces produce a per-row running balance.

Three pieces of the canonical running total:

▸PARTITION BY: where to restart the running total
▸ORDER BY: in what order to accumulate rows
▸ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: which rows to include

Walk through a small example

Account A has three transactions: $100 on Jan 1, $50 on Jan 2, $200 on Jan 5. Account B has two transactions: $300 on Jan 1, $-100 on Jan 3. After SUM OVER runs, account A's rows have running balances 100, 150, 350. Account B's rows have 300, 200. The partition restart between A and B is what PARTITION BY guarantees. Each row's value depends only on the rows of its own account, in date order.

account_id	txn_date	amount	running_balance
A	2025-01-01	100	100
A	2025-01-02	50	150
A	2025-01-05	200	350
B	2025-01-01	300	300
B	2025-01-03	-100	200

Why the frame clause is required

If you omit the frame, the engine uses a default that is almost never what you want. The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats ties in the ORDER BY column as a single peer group. If two transactions happen on the same day, RANGE groups them and assigns them the same running balance. ROWS treats each row individually and assigns each its own running balance. The default is the wrong default. Always write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.

Read this sentence three times: ROWS, not RANGE. The default in SQL is RANGE. The right answer is almost always ROWS. The single most common bug in beginner running-total queries is forgetting the frame clause and getting RANGE by default. The bug ships, the dashboard shows duplicated values for same-day transactions, and the candidate cannot reproduce it in development because the test data has unique dates.

The variant: year-to-date instead of lifetime

If the question asks 'year-to-date balance' instead of 'lifetime balance,' the only change is the PARTITION BY clause. Add the year to the partition; the running total now resets at every new year.

	/* Year-to-date balance per account */
	SELECT
	account_id,
	txn_date,
	amount,
	SUM(amount) OVER (
	PARTITION BY account_id, EXTRACT(YEAR FROM txn_date)
	ORDER BY txn_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS ytd_balance
	FROM transactions

TIP

Notice that the only change between lifetime and year-to-date is one column added to PARTITION BY. The frame stays the same. The ORDER BY stays the same. The same template handles month-to-date, quarter-to-date, fiscal-year-to-date; you just adjust the DATE_TRUNC argument. Saying 'the only change is adding the year to the partition' shows the interviewer that you see the template, not just the syntax.

What a Window Frame Actually Is

Daily Life

Interviews

Explain why RANGE (the default) groups duplicate ORDER BY values and produces wrong running totals, while ROWS does not.

This section is about the one bug that every first-time author of a running total ships at least once. It is silent, it is engine-default behavior, and it produces visibly plausible numbers that are subtly wrong. The fix is one word in the query. The reason most candidates miss it is that they have never read the docs carefully enough to know that the default is wrong.

The bug, stated plainly

If you write SUM(amount) OVER (ORDER BY txn_date) without the frame clause, SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as a single peer group. If two transactions land on the same date, RANGE adds them both at once and assigns both rows the same cumulative value. ROWS would add them one at a time and assign each its own cumulative value. The default is RANGE. The right answer is ROWS.

The default in every major engine

▸Postgres: RANGE by default (documented)
▸SQL Server: RANGE by default (documented)
▸BigQuery: RANGE by default
▸Snowflake: RANGE by default
▸Always write ROWS explicitly

Concrete example of the bug

Account A has three transactions on January 1: $100, $50, $30. The intended running total is 100, 150, 180. With RANGE (the default), all three rows tie on date, so they share a single cumulative value: 180, 180, 180. With ROWS, each row gets its own value: 100, 150, 180. Same query, same data, different answer based on which frame the engine used.

	/* The default behavior (RANGE) silently produces the wrong answer */
	SELECT
	txn_date,
	amount,
	SUM(amount) OVER (
	ORDER BY txn_date
	) AS bad_running,
	SUM(amount) OVER (
	ORDER BY txn_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS good_running
	FROM transactions
	WHERE account_id = 'A' /* bad_running: 180, 180, 180 ← three rows on the same day collapse / / good_running: 100, 150, 180 ← each row gets its own cumulative value */

•RANGE (the silent default)

Rows tied on the ORDER BY column share a single cumulative value
Three transactions on the same day all get the same running total
Looks reasonable in a quick spot check
Wrong whenever multiple rows can share an ORDER BY value

✓ROWS (almost always what you want)

Every row gets its own cumulative value
Ties on the ORDER BY column are broken by row position
Matches what an analyst means by 'running total'
Always write this explicitly; do not rely on the default

Why the default is the way it is

RANGE exists because the SQL standard interprets 'rows tied on the ORDER BY' as a logical peer group. There are questions where this is what you want: 'cumulative number of distinct days with any transaction' uses RANGE because each day is a peer group. But running-total questions are almost always per-row, not per-day. The defaults inherited from the standard do not match the common case. Modern engines have not changed it for backward compatibility.

The Postgres documentation explicitly states the default is RANGE. So do the SQL Server, BigQuery, and Snowflake docs. Most candidates default to writing no frame at all because they have never read the docs carefully. Saying 'I always write ROWS explicitly because the default is RANGE, which collapses ties' is the move that proves you have actually read the spec or been bitten by the bug.

How to remember which one to use

Memorize this rule: if you are computing a per-row cumulative value, use ROWS. If you are computing a per-value cumulative measure (where ties should aggregate), use RANGE. Running totals are per-row. Default to ROWS. The one-line check: when you write SUM OVER, immediately type ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW after the ORDER BY, before you forget.

TIP

When you type ROWS, say it out loud. 'ROWS, not RANGE, so duplicate dates do not collapse.' Saying it as you type cements the habit and signals to the interviewer that you know the difference. Many candidates type the frame clause but cannot explain why; explaining why is what scores.

At Square in 2019, a merchant dashboard team had a 'cumulative dispute volume per merchant' query that returned correct numbers for every merchant with low daily transaction counts. Then a single high-volume merchant on Black Friday hit ~40 disputes filed within the same second (the upstream system second-truncated timestamps before persisting). The dashboard read the same cumulative figure for all 40 rows of that day. Three weeks later a finance reconciliation flagged the merchant's quarterly dispute total as off by tens of thousands of dollars. The query had no explicit ROWS frame. The postmortem fix was the single word ROWS; the rubric line the team wrote into every new hire's onboarding doc was 'every SUM OVER you write should have an explicit ROWS frame, or you are shipping the same bug we did.' Naming this bug by behavior, not by syntax, is what tells the interviewer you have seen the failure mode.

Situation	Phrasing that flatlines	Phrasing that lands
You see 'running total per account'	"SUM with a GROUP BY."	"This is a running total. SUM OVER (PARTITION BY account_id ORDER BY txn_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). One row per input row, not one per group."
The interviewer asks 'what about ties on date'	"It just works."	"That's why the ROWS frame is required. RANGE (the default) treats date-ties as a peer group and assigns them the same cumulative value, which is silently wrong. ROWS gives each row its own cumulative value."
The interviewer asks for year-to-date	"I'd rewrite the query."	"One change: add EXTRACT(YEAR FROM txn_date) to the PARTITION BY. The frame and the ORDER BY stay the same. Same template handles MTD with DATE_TRUNC('month', txn_date)."
The interviewer asks for 7-day rolling	"I'd subquery."	"Same shape, change UNBOUNDED PRECEDING to 6 PRECEDING. The frame is inclusive on both sides; 6 PRECEDING through CURRENT ROW is seven rows."
The interviewer asks 'only positive transactions'	"WHERE amount > 0."	"WHERE drops the negative rows from the output; the consumer probably wants them present but excluded from the cumulative arithmetic. SUM(CASE WHEN amount > 0 THEN amount END) keeps every row and contributes only matching ones; SUM ignores NULL."

Cumulative vs Grand Total

Daily Life

Interviews

Extend running totals to 7-day rolling averages, monthly resets, and conditional cumulative sums.

Once the canonical running total works, the interviewer will pivot. 'Now do a 7-day rolling sum.' 'Now reset every month.' 'Now only count positive transactions.' These follow-ups test whether you understand the template or memorized the canonical query. Each variant changes exactly one piece of the OVER clause; the rest stays the same.

7-day rolling sum

Change the frame clause. UNBOUNDED PRECEDING becomes 6 PRECEDING. The window now contains seven rows: the current row plus the six rows immediately before it. Everything else stays the same.

	/* 7-day rolling sum per account */
	SELECT
	account_id,
	txn_date,
	amount,
	SUM(amount) OVER (
	PARTITION BY account_id
	ORDER BY txn_date
	ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
	) AS rolling_7d
	FROM transactions

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is seven rows, not six. The frame is inclusive on both sides. Count carefully: 6 PRECEDING through CURRENT ROW means six rows before plus the current row, which is seven rows total.

Monthly reset

Change the partition. Add DATE_TRUNC('month', txn_date) to the PARTITION BY clause. The running total now restarts at the first of each month.

	/* Month-to-date balance per account */
	SELECT
	account_id,
	txn_date,
	amount,
	SUM(amount) OVER (
	PARTITION BY account_id, DATE_TRUNC('month', txn_date)
	ORDER BY txn_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS mtd_balance
	FROM transactions

Trace it. On January 31, the running balance reflects all of January's transactions. On February 1, it resets to whatever happened on February 1. DATE_TRUNC produces a value that changes once per month, and adding it to PARTITION BY treats each month as a separate partition.

Conditional running totals

'Only count positive transactions toward the running total.' Wrap the column in a CASE expression. SUM ignores NULLs, so anything you do not want to count, return NULL.

	/* Running total of only positive amounts (deposits, not withdrawals) */
	SELECT
	account_id,
	txn_date,
	amount,
	SUM(
	CASE
	WHEN amount > 0 THEN amount
	END
	) OVER (
	PARTITION BY account_id
	ORDER BY txn_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS running_deposits
	FROM transactions

CASE returns the amount when the row qualifies and NULL otherwise. SUM ignores NULLs, so non-qualifying rows do not affect the running total. The output still has one row per input transaction; only the qualifying rows' values contribute to the cumulative sum.

The pattern, restated

Every variant changes one of three things. The frame clause for window size (unbounded vs N preceding). The partition for the reset boundary (lifetime vs year vs month). The source expression for what counts (raw column vs CASE). Knowing this lets you answer any follow-up by changing one piece. When the interviewer asks 'now do X,' say out loud which of the three pieces you are changing. That meta-statement is the move.

The template, restated as three knobs:

▸Frame clause for window size (lifetime, rolling N, year-to-date)
▸Partition column for reset boundary (per-account, per-year, per-month)
▸Source expression for what counts (raw column, CASE expression)

•Answer that rewrites from scratch each time

Treats each variant as a new problem
Has to remember the full syntax every time
Slow on follow-ups, runs out of time on the third probe
Cannot articulate the relationship between the variants

✓Answer that adjusts one piece at a time

Recognizes that all variants share the same canonical shape
Identifies which piece needs to change and says so out loud
Handles four follow-ups in the time most candidates handle one
Demonstrates that the template is internalized

Reading the Running Total Row-by-Row

Daily Life

Interviews

Discuss partition-level parallelism, pre-aggregation tradeoffs, and why running totals are expensive on streaming systems.

After the variants, the interviewer will ask about scale. 'How does this perform on a billion-row table?' The answer at the entry level does not need to be a full architecture discussion. It needs to cover one idea: pre-aggregate. The running total runs on the daily total, not on every individual transaction. That single move is what separates the candidate who knows window functions from the candidate who knows when not to use them on raw data.

The scale problem

Running totals run cheaply when the partition is small. They run expensively when the partition has millions of rows. A billion-row transactions table with a million accounts means an average of a thousand rows per partition, which is fine. The same table with one account holding all the transactions means a billion rows in one partition, which is catastrophic. The window function cannot parallelize within a partition; one worker has to process the whole thing. Knowing this matters because it tells you where the cost goes.

The pre-aggregation move

If the consumer wants the running total at the daily level, do not run the window function on every transaction. Roll up to daily first, then run the window function on the daily totals. A billion-row transactions table might collapse to a few hundred million daily rows. The cumulative sum then runs on the smaller table. The numbers are identical because addition is associative.

	/* Pre-aggregate to daily, then compute the running total */
	WITH daily AS (
	SELECT
	account_id,
	txn_date,
	SUM(amount) AS daily_net
	FROM transactions
	GROUP BY account_id, txn_date
	)

	SELECT
	account_id,
	txn_date,
	SUM(daily_net) OVER (
	PARTITION BY account_id
	ORDER BY txn_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS running_balance
	FROM daily

The CTE produces a smaller table: one row per (account, day) instead of one row per transaction. The window function runs on the smaller table. Same end result, much less work. Mention this approach when the interviewer asks about scale; it shows you understand that the right grain for the window function is the grain the consumer needs, not the grain of the raw source.

Pre-aggregation is the single most useful optimization for window functions on large tables. The reason: window functions cannot use indexes to skip rows the way GROUP BY can. They have to materialize the partition in memory and walk it in order. Smaller partitions mean less memory and faster walks. Saying 'I would pre-aggregate to the reporting grain before the window function' is the optimization sentence that proves you have tuned this kind of query.

The supporting index

The pre-aggregation needs to read the source efficiently. An index on (account_id, txn_date) lets the engine compute the daily aggregate in one indexed scan per account. Without the index, the engine scans the whole table and aggregates in memory. Mention the index unprompted: 'for this to scale, I would want an index on (account_id, txn_date) so the daily aggregate runs as an indexed scan per account.'

The closing summary

Close with a three-sentence wrap. 'I used SUM OVER with PARTITION BY account, ORDER BY date, and an explicit ROWS frame from unbounded preceding to current row. I would always write ROWS, not RANGE, because the default RANGE collapses ties on the same date and produces the wrong running total. For scale, I would pre-aggregate to daily grain inside a CTE before running the window function, and ensure the supporting index on (account_id, date) exists.' Three sentences. Each names a different layer: the query, the silent bug, the scale concern. The shape generalizes.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a Microsoft data engineering phone screen. The interviewer asks: 'For each transaction, return the account's running balance.'

You say: 'This is a running total. I will use SUM OVER, partitioned by account, ordered by date, with the frame from unbounded preceding to current row.'

You write the canonical query in twelve lines, including ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.

You narrate the frame clause out loud: 'ROWS, not RANGE, because two transactions on the same day should each get their own cumulative value rather than sharing one.'

Follow-up: 'Now make it year-to-date.' You say: 'Only the PARTITION BY changes. Add EXTRACT(YEAR FROM txn_date) so the running total resets at the start of each year. Everything else stays the same.'

KEY TAKEAWAYS

The canonical running total is SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), four pieces that produce one cumulative value per row.

Always write the frame explicitly. The default is RANGE, which treats rows tied on the ORDER BY column as one peer group: three transactions on the same date read 180, 180, 180 instead of 100, 150, 180.

Use ROWS for per-row cumulative values and RANGE only when ties genuinely should aggregate. Running totals are per-row, so the bug hides in development because test data has unique dates.

Every follow-up changes exactly one of three knobs: the frame for window size (6 PRECEDING gives a seven-row window, not six), the PARTITION BY for the reset boundary, and the source expression for what counts.

Conditional running totals wrap the column in CASE and rely on SUM ignoring NULLs, so non-qualifying rows stay in the output but contribute nothing to the cumulative value.

For scale, pre-aggregate to the reporting grain in a CTE before the window function, since window functions cannot skip rows via an index and must walk the whole partition in order; back it with an index on (account_id, txn_date).

The cumulative sum that trips up candidates who forget frame clauses

Category: SQL
Difficulty: beginner
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: "Show Me the Running Balance", SUM OVER (ORDER BY ...) Basics, What a Window Frame Actually Is, Cumulative vs Grand Total, Reading the Running Total Row-by-Row

Lesson Sections

"Show Me the Running Balance" (concepts: sqlAggregateOver)
The English of the question always sounds simple. 'For each transaction, show the account's running balance.' 'Show me revenue accumulated since the start of the year, broken down by day.' 'Compute the cumulative number of users who have ever logged in.' Each one is a running total. Each one wants the same SQL shape: a SUM with an OVER clause that tells the engine which rows to add up. What 'running' means, and how to spot it Why companies care Running totals power every financial and product-an
SUM OVER (ORDER BY ...) Basics (concepts: sqlWindowFrame)
The canonical query has the same shape every time. SUM of the column to total, OVER with three parts: PARTITION BY for the group, ORDER BY for the time column, and a frame clause that says which rows to include. Memorize this shape. Type it without thinking. The query you should be able to write from memory Read the query out loud Each clause does a specific job. SUM(amount) is the value you are accumulating. PARTITION BY account_id makes each account its own running total; the balance restarts
What a Window Frame Actually Is (concepts: sqlWindowFrame)
This section is about the one bug that every first-time author of a running total ships at least once. It is silent, it is engine-default behavior, and it produces visibly plausible numbers that are subtly wrong. The fix is one word in the query. The reason most candidates miss it is that they have never read the docs carefully enough to know that the default is wrong. The bug, stated plainly Concrete example of the bug Account A has three transactions on January 1: $100, $50, $30. The intended
Cumulative vs Grand Total (concepts: sqlWindowFrame)
Once the canonical running total works, the interviewer will pivot. 'Now do a 7-day rolling sum.' 'Now reset every month.' 'Now only count positive transactions.' These follow-ups test whether you understand the template or memorized the canonical query. Each variant changes exactly one piece of the OVER clause; the rest stays the same. 7-day rolling sum Change the frame clause. UNBOUNDED PRECEDING becomes 6 PRECEDING. The window now contains seven rows: the current row plus the six rows immedia
Reading the Running Total Row-by-Row (concepts: sqlAggregateOver)
After the variants, the interviewer will ask about scale. 'How does this perform on a billion-row table?' The answer at the entry level does not need to be a full architecture discussion. It needs to cover one idea: pre-aggregate. The running total runs on the daily total, not on every individual transaction. That single move is what separates the candidate who knows window functions from the candidate who knows when not to use them on raw data. The scale problem Running totals run cheaply when