For each PAID order, return the customer's running total of paid amounts ordered by order_ts ascending.
Sort the final result by customer_id, then order_ts, then order_id.
Refunded, cancelled, delivered, and NULL-status orders are excluded from both the rows and the totals. If a customer has two paid orders with the same order_ts, add them one at a time in order_id order, so each row gets its own running total.
Returncustomer_idorder_ts::DATE AS order_dateamount_usdrunning_total
About this problem
You practise a per-customer running total over a filtered set of rows. From orders, you keep only paid orders and give each one a running_total of that customer’s paid amount_usd in order_ts order, alongside customer_id, the order date and the amount. The output is sorted by customer_id, order_ts and order_id. The skill is filtering before the window function runs, partitioning by customer, and making the running sum advance row by row even when two orders share a timestamp.
The first trap is where the status filter sits. If you compute the window over all orders and then keep only paid rows in an outer query, the refunded, cancelled, delivered and NULL-status amounts are already baked into the totals. The output looks right, because only paid orders appear, but the sums are wrong. Listing exclusions with NOT IN is another slip, since such a list can miss statuses you didn’t think of. The second trap is ties. The sample has one customer with two paid orders at the same order_ts. With ORDER BY order_ts and the default RANGE frame, those two rows are peers and both get the same total, including each other’s amounts. Without PARTITION BY customer_id, totals run across customers.
Read the lesson: Running Totals & Moving Averages (P16) →