the AI wrote me a running total that was 'almost right'. almost right is the worst kind

ok quick one because this bit me today and i want to save someone an afternoon.

asked an ai assistant for a running total of revenue. it gave me this, which looks totally fine:

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

ran it, numbers looked plausible, almost shipped it to the dashboard. then i noticed the running total jumped by the same amount on every row with the same date. if there were 3 orders on march 4th, all 3 rows already showed the total for the whole day.

here's why. when you put ORDER BY in a window and no frame clause, the default frame is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE means "every row whose order value is up to and including mine", so rows that tie on order_date (peers) all get included together. if you want a row by row running total you need ROWS, plus a tie breaker so the order is deterministic:

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

the default is the same in postgres, sql server and bigquery (check yours). if you actually want one running total per day, aggregate first and then window, which is clearer anyway:

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

(no ties after grouping, so RANGE vs ROWS doesn't matter there.)

the ai wasn't wrong exactly. it answered a slightly different question than the one i asked and the output looked right. that scares me more than obviously broken code. obviously broken code at least fails loudly lol