AI-Written SQL: Why Two Orders Can Share a Running Total

You ask an AI assistant for a running total of units sold. The query shows an order for four units, but the total on that row is ten. The next order adds six units, yet its total is also ten. At the end of the report the numbers add up correctly, so a quick reconciliation will not catch the problem.

Both orders share a timestamp. The query is answering a reasonable question: how many units had been booked through that timestamp? The report’s reader may be asking a different one: how many units had been booked after this particular order? The distinction belongs in the brief you give the assistant and in the check you run before accepting its SQL.

Read the order inside the calculation

A window sum lets a report retain the order rows while adding a total alongside each one. The window frame determines which rows contribute to each calculation. The PostgreSQL 18 tutorial explains that when an ordered window uses its default frame, the calculation includes the current row and its peers: rows that compare equal under the window’s ordering. The SQLite window function documentation specifies the same behaviour for its default aggregate window frame.

Consider three fictional orders. Order 101 booked four units at 09:00, order 102 booked six units at 09:00, and order 103 booked three units at 09:05. For this small example, the times are zero-padded strings on the same day. The data represents three order rows, with no cancellations or missing timestamps.

WITH orders(order_id, booked_at, units) AS (
  VALUES (101, '09:00', 4), (102, '09:00', 6), (103, '09:05', 3)
)
SELECT order_id, booked_at, units,
       SUM(units) OVER (ORDER BY booked_at) AS by_time,
       SUM(units) OVER (
         ORDER BY booked_at, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS by_order
FROM orders
ORDER BY booked_at, order_id;

I ran this example in SQLite 3.53.1. The by_time column returned 10, 10 and 13. The by_order column returned 4, 10 and 13. The first column includes both 09:00 orders in each 09:00 total. The second steps through the orders individually in the sequence specified inside its window.

Choose what a tie means for the reader

A report about the state of the shop at each recorded time can legitimately include all the orders that share that time. If the report needs only one row per timestamp, a clearer presentation may be to aggregate units by timestamp first and then calculate the running total on those rows. That changes the level of detail: the reader sees time points instead of individual orders.

If the report needs one step per order, use a row-based frame and supply an ordering that distinguishes tied orders. In the example, the order ID supplies a stable tie-breaker. That is an agreed reporting convention. It does not prove that order 101 happened before order 102 in the real world. If exact chronology matters, ask whether the source records a reliable sequence or a more precise timestamp.

There are two separate orders in the example query. The ORDER BY inside OVER governs the calculation. The final ORDER BY governs the display. Sorting the displayed rows by order ID does not change which rows the timestamp-based calculation includes.

Decide what happened before the report starts

Suppose the shop’s report begins at 09:05. Should the first displayed total be three or thirteen? If the report is about units booked during the visible period, three is consistent with that scope. If it is about the whole day and merely hides earlier rows, thirteen is the intended figure. In the second case, calculate the total over the full relevant day before filtering the displayed rows in an outer query.

This is a separate decision from handling tied timestamps. An assistant can get the tie-breaker right and still start the total at the wrong point. State both the accumulation period and the display period in the brief.

Verify more than the last number

A useful review begins with the row at which the two interpretations diverge. A fixture with unique timestamps would hide this issue, and comparing only the final value would miss it as well. When reviewing AI-generated SQL, give the assistant a few records and specify what every returned row should show.

• Include a timestamp tie. Give the tied orders different quantities and check whether the total should step or repeat.
• Include a later record. Verify that the total carries forward once the tied group is behind it.
• Check the grain of the input. Decide whether a row represents an order or an order line before summing quantities.
• Check resets. If the total should restart for each shop or day, specify the grouping and include a boundary case.
• Use the target database. Run the test in the engine your report actually uses and confirm its documented frame rules.

The input grain deserves its own check because a window calculation cannot repair an earlier mistake in the rows it receives. An order that disappears before the sum is calculated will not contribute to it. MaryChuks.com’s guide to filters on a LEFT JOIN explains one way that can happen.

For a handover, write a sentence that a colleague can test: “Show cumulative units after each order, using order ID to break timestamp ties, and restart for each shop.” Or specify a total through each timestamp if that is the business question. The goal is to make the first surprising row explainable, rather than relying on a final total that both interpretations happen to share.

Sources

• PostgreSQL 18 documentation: Window Functions.
• SQLite documentation: Window Functions.

Documentation checked on 5 November 2026. The order data is fictional; the example query was tested in SQLite.