How Do You Calculate a Running Total with Window Functions?
Learn how to compute a running total in SQL using SUM() with a window function, including PARTITION BY and frame clauses.
Expected Interview Answer
A running total is calculated with SUM(column) OVER (ORDER BY sequence_column), which sums the target column across all rows from the start of the window up to and including the current row, using the default frame of RANGE UNBOUNDED PRECEDING to CURRENT ROW.
Because the window function evaluates per row rather than collapsing rows, each row displays its own value alongside the accumulated sum of everything before it in the specified order. Adding PARTITION BY resets the running total independently for each group, such as computing a running total per customer instead of across the whole table. It is important to be explicit about the frame clause (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) when ties exist in the ORDER BY column, since the default RANGE frame includes all tied peer rows in the same running-total step rather than one row at a time.
- Computes cumulative sums without a self-join or subquery
- PARTITION BY resets the running total per group
- One query pass instead of iterative application-side accumulation
- Explicit ROWS framing avoids surprises with tied ORDER BY values
AI Mentor Explanation
Think of a scorer tracking a team's total runs ball by ball: after every delivery, the scoreboard shows not just that ball's runs but the cumulative team score up to that point in the innings. Each ball's row still shows the individual runs scored, plus the running total accumulated so far. SUM() OVER (ORDER BY ball_number) does exactly this, adding each row's value to everything that came before it in the defined order.
Step-by-Step Explanation
Step 1
Pick the column to accumulate
Choose the numeric column to sum, such as amount, revenue, or points.
Step 2
Write SUM() with OVER and ORDER BY
Use SUM(column) OVER (ORDER BY sequence_column) to accumulate values in the specified order.
Step 3
Add PARTITION BY if needed
Include PARTITION BY to reset the running total independently for each group, such as per customer.
Step 4
Set the frame explicitly for ties
Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to guarantee row-by-row accumulation when the ORDER BY column has duplicate values.
What Interviewer Expects
- Correct SUM() OVER (ORDER BY ...) syntax for a running total
- Awareness that PARTITION BY resets the running total per group
- Understanding of the default RANGE frame versus an explicit ROWS frame
- Knowledge that ties in ORDER BY can affect results without an explicit ROWS frame
Common Mistakes
- Forgetting ORDER BY, which leaves the accumulation order undefined
- Not realizing the default frame is RANGE, which can group tied rows together unexpectedly
- Using a correlated subquery or self-join instead of a window function
- Omitting PARTITION BY when the running total should reset per group
Best Answer (HR Friendly)
“To get a running total, I use SUM() with an OVER clause ordered by whatever sequence makes sense, like date, so each row shows the cumulative sum of everything up to that point. If I need separate running totals per group, like per customer, I add PARTITION BY on top of that.”
Code Example
SELECT
store_id,
sale_date,
daily_sales,
SUM(daily_sales) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM DailySales;
-- The running total resets to zero at the start of every store's
-- rows and accumulates row by row through sale_date order.Follow-up Questions
- What is the difference between the default RANGE frame and an explicit ROWS frame for a running total?
- How would you compute a running total that resets every month within the same query?
- How would you calculate a moving average instead of a full running total?
- What happens to a running total if two rows share the same ORDER BY value under the default frame?
MCQ Practice
1. Which window function expression computes a running total ordered by date?
SUM(amount) OVER (ORDER BY date) accumulates the amount for every row up through the current row in date order.
2. What does adding PARTITION BY to a running total window function do?
PARTITION BY restarts the cumulative sum from zero for each group, such as computing separate running totals per customer.
3. Why might an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame be necessary?
The default RANGE frame includes all rows tied on the ORDER BY value in the same step, while ROWS forces strict row-by-row accumulation.
Flash Cards
How do you compute a running total in SQL? — SUM(column) OVER (ORDER BY sequence_column), summing all rows up to and including the current row.
How do you reset a running total per group? — Add PARTITION BY <group_column> to the OVER clause alongside ORDER BY.
What frame does a running total use by default? — RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which can include tied peer rows together.
When should you use an explicit ROWS frame for a running total? — When the ORDER BY column may contain duplicate values and strict row-by-row accumulation is required.