The same SUM(amount) OVER expression can accumulate individual transactions or include every transaction on the current date at once. Both execute successfully but answer different questions. PostgreSQL 18 documentation supplies the semantics below, alongside a DuckDB example of date ranges. The examples are derivations, not production performance measurements.
Write a window contract before writing SQL
Reliable enterprise Text-to-SQL requires queries to answer the intended business question. For a window calculation, first define whether a row is a transaction, daily aggregate or snapshot; whether partitions represent customers, warehouses or companies; whether order represents business sequence or presentation; and whether the frame covers rows, days or the full partition. Adding ORDER BY without these definitions merely makes the output look ordered.
Use three constructed transactions, all in department A: id=1, business_date=2026-10-01, amount=100; id=2, the same date, amount=50; id=3, business_date=2026-10-03, amount=20. IDs are unique, amounts are non-null integers, and dates have already been determined in one business time zone. The total is 170. No row exists for October 2, but absence alone does not establish zero activity.
Windows generally retain input rows, whereas grouping changes grain.[2] For cumulative daily settlement, aggregate by department and date first. For a transaction ledger, retain transaction rows and define their ordering. Mixing those grains can repeat a daily cumulative figure across transactions and make a correct calculation look like duplicated posting.
Why a default frame includes both transactions on the same date
Consider SUM(amount) OVER (PARTITION BY dept ORDER BY business_date). Under the PostgreSQL default discussed here, an ordered window with no explicit frame extends through the current row’s last ordering peer: rows sharing the ordering value.[1] Displayed by ID, the totals are 150, 150 and 170. The first 150 is not accidental double counting; both same-date transactions belong to its frame.
For accumulation in an explicitly chosen transaction order, use SUM(amount) OVER (PARTITION BY dept ORDER BY business_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The results are 100, 150 and 170. ID defines a stable order for this example only. A real system must determine whether it is an appropriate same-day sequencing rule; sorting random identifiers does not reconstruct event chronology.
ROWS defines boundaries by positions in the sorted rows; RANGE uses ordering values and value ranges. Explicit ROWS with date-only ordering still leaves the relative order of tied transactions unresolved. A final ORDER BY business_date, id controls presentation but cannot supply a missing order inside OVER. Specify window order and output order separately.
For end-of-day accumulation, 150, 150 and 170 may be appropriate, but readers must not sum the repeated cumulative figures across transaction rows. A clearer daily representation has 150 on October 1 and 20 on October 3, followed by cumulative values 150 and 170. Correctness follows the contract, not the smoother-looking chart.
The last two rows are not the last two calendar days
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW includes the current and preceding row. Ordered by date and ID, the third row sums to 50+20=70, even though the preceding row occurred on October 1. This is not the total for October 2–3. Expanding the frame to six preceding rows likewise does not automatically produce a seven-day window.
For a DATE-valued business_date, ordering by that date with RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW selects existing records within the current and previous date. The October 3 result is 20; both October 1 rows return 150. PostgreSQL restricts ordering expressions for offset RANGE frames. This example uses one date ordering key; do not copy the date-plus-ID ordering from the transaction frame into it. [4]
DuckDB’s official windowing article illustrates moving aggregation over date ranges with missing dates.[3] It was published in 2021. This article uses its explanation of the mechanism, not its historical performance numbers as a current speed promise. A range frame selects existing records; it neither creates absent dates nor decides whether absence means no activity or incomplete collection.
If the output requires one row per day and an average with a fixed calendar-day denominator, use a calendar relation and a completeness policy. Fill confirmed inactivity with zero; retain unknown or provisional status for incomplete sources. A TIMESTAMP-based rolling range is another definition. Calendar days, the previous 48 hours and two business days are not interchangeable. Define time zone, business calendar and boundaries before choosing a date or timestamp field.
The last value may only be the end of the current frame
LAST_VALUE(amount) is easily mistaken for the department’s final amount, but it reads the last row of the current frame.[1] With unique business_date, id ordering and the default frame, this example returns 100, 50 and 20. To show the final transaction amount under that order on every row, specify the complete frame, such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. All three results then become 20.
The final transaction amount still is not a closing balance. A balance is a state; a transaction amount is a flow. Use the appropriate field and calculation. Nulls, reversals and backfilled transactions also require a definition of last: last occurrence, effective change or ingestion. A function name cannot choose that business meaning.
LAG(amount) retrieves the preceding row in the chosen order, not automatically yesterday’s value. The third row’s predecessor is the October 1 amount of 50. To compare yesterday’s aggregate, first establish daily grain, then join on the previous business date or use LAG on a complete calendar sequence. Window functions do not all use frames in the same way; changing SUM’s frame does not redefine every function.
A display filter can silently restart accumulation
Filtering with WHERE business_date >= DATE '2026-10-03' in the same SELECT removes the first two rows before window accumulation, so the third row returns 20. Accumulating all three in an inner query and displaying only October 3 in an outer query preserves 170. PostgreSQL’s tutorial explains that windows operate on input already shaped by same-level filtering and grouping; an outer query can separate calculation scope from display scope.[2]
One possible structure is: WITH w AS (SELECT id, dept, business_date, SUM(amount) OVER (PARTITION BY dept ORDER BY business_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM transactions) SELECT * FROM w WHERE business_date >= DATE '2026-10-03' ORDER BY business_date, id; The table and columns are hypothetical and must be replaced with authorized objects with approved business definitions.
Moving a display filter outward is appropriate only for history that legitimately belongs in the calculation. Tenant boundaries, row-level permissions and business exclusions must still constrain authorized input. Do not compute over unauthorized records merely to preserve a cumulative figure. Even a single output row can disclose other data through its total.
Large tables need not always be recomputed from their earliest record. A validated opening balance plus current-period changes may work, but requires an opening cutoff, data version and policy for backfills. Truncating history to reduce scan cost does not justify labeling a period total as an all-time total. Late transactions can change every subsequent running value, so define the recomputation scope.
Validate small counterexamples and an independent calculation
Record hand-derived expectations for the three rows: date-default totals 150, 150, 170; uniquely ordered row totals 100, 150, 170; third-row two-row moving sum 70; two-calendar-day range sum 20; inner accumulation followed by display filtering 170, versus same-level filtering 20. These are semantic derivations for constructed inputs, not execution results from the target database. Run them on the actual engine, version and field types and retain the results.
Add counterexamples: change insertion order without changing business ordering keys; introduce another department to test partition isolation; add a same-day transaction and check transaction and daily contracts separately; insert a date-boundary record to check time-zone conversion; remove an intermediate day to distinguish row and date frames; add nulls and negative amounts to test the allowed metric semantics. A running total that permits negative flows need not be monotonic.
For small samples, use independent date-condition aggregates or item-by-item sums as references instead of copying the original window expression into the test. Compare results by business key and verify row counts, tie handling and final presentation order. At scale, inspect execution plans, sorting costs and skew separately. Acceptable latency and correct business meaning are independent acceptance criteria.
Version the grain, partition keys, stable ordering keys, frame, calculation interval, display interval, null policy and missing-date policy as a query contract alongside the report or analytics answer. Rerun boundary cases after data changes, engine migrations and changes to model-generated SQL. Explicit frames cannot prevent every error, but they expose hidden business choices to review and reproducible delivery.