Running Total of Revenue per Customer

Difficulty: Easy · Topics: window-functions, running-total, frames · Asked at: Amazon, Stripe, Shopify

Problem

For each order, return customer_id, order_id, order_date, amount and running_total: the customer’s cumulative spend up to and including this order, processing orders by order_date then order_id. Order the output by customer_id, order_date, order_id.

Schema and sample data

CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
(1, 1, '2026-01-01', 100),(2, 1, '2026-01-05', 50),(3, 1, '2026-01-05', 25),
(4, 2, '2026-01-02', 300),(5, 2, '2026-01-09', 20),(6, 1, '2026-02-01', 75);

Expected output

Hints

Solution

SELECT customer_id, order_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id
                         ORDER BY order_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
ORDER BY customer_id, order_date, order_id;

Explanation

With only ORDER BY order_date, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Both 2026-01-05 orders are peers and both would show 175, which isn’t a running total per order. Adding a unique tie-breaker (order_id) and an explicit ROWS frame produces 150 then 175.

Follow-up questions