Context

Running totals are a common SQL pattern in reporting, finance, and product analytics. Interviewers ask this to test whether you understand cumulative calculations and the SQL features used to produce them efficiently.

Core question

Explain how you would calculate a running total in SQL. In your answer, describe:

1. What a running total is
2. Which SQL feature is typically used to calculate it
3. Why ORDER BY is required in the calculation
4. How partitioning changes the result
5. What common mistakes can lead to incorrect totals

Scope guidance

The interviewer is usually looking for a practical explanation centered on window functions, especially SUM() OVER (...). You should be able to explain the difference between a regular aggregate and a windowed aggregate, and give a simple example such as cumulative sales by date or cumulative revenue by customer.

Running Total

A running total is a cumulative sum where each row includes the current row's value plus all prior relevant rows. It is typically calculated over an ordered sequence such as dates, transaction IDs, or event timestamps.

SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Window Function

A window function performs a calculation across a set of rows related to the current row without collapsing the result into one row per group. This makes it ideal for returning both the original row-level data and a cumulative metric in the same query.

SUM(amount) OVER (ORDER BY order_date)

ORDER BY in the Window

The ORDER BY inside the OVER (...) clause defines the sequence in which rows are accumulated. Without it, you do not get a running total—you get the same total repeated across rows in the window.

SUM(amount) OVER (ORDER BY transaction_date, id)

PARTITION BY

The PARTITION BY clause resets the running total for each subgroup, such as each customer, region, or product category. This is useful when you need separate cumulative totals within each logical segment.

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)

Window Frame Behavior

The window frame determines which rows are included relative to the current row. In cumulative calculations, you often want all rows from the start of the partition through the current row, which can be expressed explicitly for clarity.

SUM(amount) OVER (
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)