Running total in SQL with SUM OVER (and the same-day trap)

Why the usual answer is wrong

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

Orders 1 and 2 are on the same day. With only ORDER BY order_date, the window adds up every row with the same date at once, so order 1 already shows 150.

The answer

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

A window with ORDER BY but no frame uses RANGE: rows that tie on the order are added together. Order by something unique (date, then order id) and say ROWS so each row adds only itself.

Add PARTITION BY customer_id to restart the total for each customer.

SELECT o.order_id, o.order_date, o.amount,
  (SELECT SUM(x.amount) FROM orders x
   WHERE x.order_date < o.order_date
      OR (x.order_date = o.order_date AND x.order_id <= o.order_id)) AS running_total
FROM orders o
ORDER BY o.order_date, o.order_id;