Running Total per Customer

The query

A running total by group is a plain running total with one extra piece: PARTITION BY. Here it is on the orders table, one total per customer:

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

Customer 101 has three orders, so the column reads 249.99, 2249.98 and 2329.97. On the next row, customer 102's total starts over at 1199.99. Three customers (103, 107 and 110) have a single order, so their running total is just that order.

What goes wrong without PARTITION BY

Drop the partition and the window runs across the whole table in date order:

SELECT customer_id, order_id, order_date,
       ROUND(SUM(total_amount) OVER (ORDER BY order_date, order_id), 2) AS running_total
FROM orders
ORDER BY customer_id, order_date;

Now customer 101's third order shows 7249.9, which includes other people's orders. Bob's first order (customer 102) shows 1449.98 instead of 1199.99, because Alice's January order came first. The numbers look plausible, which is why this bug is easy to miss.

What goes wrong without ORDER BY

Keep the partition but drop the ORDER BY, and every row gets the customer's grand total:

SELECT customer_id, order_id, total_amount,
       ROUND(SUM(total_amount) OVER (PARTITION BY customer_id), 2) AS total
FROM orders
WHERE customer_id IN (101, 104);

All three of customer 101's rows show 2329.97, and all three of customer 104's rows show 569.97. That's useful for a percentage of a group total, but it isn't a running total. The ORDER BY inside OVER is what turns a group sum into a cumulative one.

Ties: ROWS vs RANGE

With ORDER BY and no frame, the default frame is RANGE, which treats rows with the same order value as one step. Two orders on the same day get the same running total:

WITH t(customer_id, order_date, amount) AS (
  VALUES (1, '2024-05-01', 100), (1, '2024-05-01', 50), (1, '2024-05-03', 20)
)
SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS range_total,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM t;

Both May 1 rows show 150 under RANGE, while ROWS gives 100 and then 150. Adding a unique tiebreaker such as order_id to the ORDER BY, as the solution does, gives the same row by row result. The real orders table has no same-day orders for one customer, but saying this out loud in an interview shows you know how frames work.

Without window functions

If a database has no window functions, a correlated subquery sums every earlier order of the same customer:

SELECT o.customer_id, o.order_id, o.order_date, o.total_amount,
       ROUND((SELECT SUM(o2.total_amount)
              FROM orders o2
              WHERE o2.customer_id = o.customer_id
                AND (o2.order_date < o.order_date
                     OR (o2.order_date = o.order_date AND o2.order_id <= o.order_id))), 2)
         AS customer_running_total
FROM orders o
ORDER BY o.customer_id, o.order_date, o.order_id;

It returns the same 15 rows, but it rescans the customer's orders for every row. The window version reads the table once, so prefer it whenever it's available.

Other groups work the same way

The group can be anything: a product, a region, a category. On the monthly_sales table, SUM(revenue) OVER (PARTITION BY product_name ORDER BY sale_month) gives MacBook Pro 25000, 55000 and 83000 across January to March 2025, while AirPods Pro restarts at 8000.

Filters run before the window. Add WHERE status = 'delivered' and the result drops to 11 rows, so Dave's pending September order no longer counts toward his total.

What to say in the interview

Name the three parts out loud: the sum, the partition that resets it, and the order that accumulates it. Mention that you added a tiebreaker to the ORDER BY, and say why a GROUP BY can't do this. For more patterns like this one, see the window functions guide and Cumulative Sum Over Time.