Context

Running totals are common in reporting dashboards, finance, and product analytics. Interviewers ask this to test whether you understand cumulative calculations, row ordering, and when to use window functions instead of grouped aggregates.

Core Question

Explain how you would compute a running total in SQL. Your answer should cover:

1. What a running total is
2. Why SUM() OVER (...) is typically the right approach
3. How ORDER BY changes the result
4. When you need to aggregate first before applying the running total
5. Common mistakes, such as missing tie-breakers or confusing GROUP BY with window functions

Scope Guidance

Assume the interviewer expects a practical explanation using PostgreSQL syntax, not just a definition. You should be able to describe the pattern, show a short example query, and explain edge cases like duplicate dates, partitions, and NULL values.

Window Function for Cumulative Sum

A running total is usually computed with SUM(...) OVER (...). Unlike GROUP BY, a window function keeps each row in the result while also calculating a cumulative value across related rows.

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

Importance of ORDER BY

The running total depends on row sequence, so ORDER BY inside the window definition is essential. Without it, you do not get a cumulative result; you get the same total repeated across rows in the partition.

SUM(amount) OVER (ORDER BY order_date, order_id)

Partitioned Running Totals

Use PARTITION BY when the cumulative sum should restart for each group, such as per customer, product, or region. This is common in segmented reporting.

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
       ) AS customer_running_total
FROM orders;

Pre-Aggregation Before Running Total

If the business question asks for a daily or monthly running total, you often need to aggregate transactions to that grain first. Then apply the window function to the aggregated result.

WITH daily_sales AS (
  SELECT order_date, SUM(amount) AS daily_amount
  FROM orders
  GROUP BY order_date
)
SELECT order_date, daily_amount,
       SUM(daily_amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

Deterministic Ordering

If multiple rows share the same sort key, such as the same date, the cumulative order may be ambiguous unless you add a tie-breaker. Including a unique column like order_id makes the result deterministic.

SUM(amount) OVER (ORDER BY order_date, order_id)