Which Readings Belong in This Rolling Total?

Two usage readings arrive with the same timestamp. Botie expects a running total to increase after each record, but both records suddenly display the total after both readings. The database has followed its frame rule; we had not actually specified the row-by-row rule we intended.

A window frame names the rows allowed to contribute to the current answer. We will compare a running total with a two-reading total, then decide what a tied timestamp and a missing day should do.

Order within each pump and sum the current reading with its immediately preceding reading.

[
  {
    "event_id": 1,
    "asset_id": "P7",
    "recent_total": 2
  },
  {
    "event_id": 2,
    "asset_id": "P7",
    "recent_total": 5
  },
  {
    "event_id": 3,
    "asset_id": "P7",
    "recent_total": 10
  },
  {
    "event_id": 4,
    "asset_id": "P8",
    "recent_total": 10
  }
]

The partition selects the pump’s history. The ordering puts that history in sequence. The frame selects the portion of that ordered history used for the current aggregate.

These choices answer different questions. Correctly partitioning by asset does not make a two-day frame equivalent to a two-reading frame. Correctly ordering by time does not say whether all earlier records should contribute.

Say the calculation aloud before choosing its frame: current observation plus one preceding observation.

With an ordered aggregate and no explicit frame, SQLite uses a RANGE frame from the partition’s start through the current row’s peers. When timestamp alone orders the window, equal timestamps are peers.

In this example, the two 08:00 readings therefore both receive 5. This is useful when simultaneous readings should share a cumulative result. It is surprising if we expected event-by-event accumulation.

The outer event_id ordering displays the rows consistently without changing the window’s timestamp-only peer groups.

CREATE TABLE usage(event_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, measured_at TEXT NOT NULL, amount INTEGER);
INSERT INTO usage VALUES (1,'P7','2026-01-01 08:00:00',2),(2,'P7','2026-01-01 08:00:00',3),(3,'P7','2026-01-03 08:00:00',7),(4,'P8','2026-01-01 08:00:00',10);

SELECT event_id,
 SUM(amount) OVER (PARTITION BY asset_id ORDER BY measured_at) AS peer_total,
 SUM(amount) OVER (PARTITION BY asset_id ORDER BY measured_at, event_id
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_total
FROM usage WHERE asset_id='P7' ORDER BY event_id;

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW includes the previous ordered record and the current one. The first row has no predecessor, so its frame contains only itself.

A second ordering column makes the sequence deterministic when timestamps tie. SUM ignores NULL amounts; if the entire frame is unknown, its result is NULL. We do not replace missing measurements with zero without a separate decision.

A two-row frame can contain fewer than two known values.

CREATE TABLE usage(event_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, measured_at TEXT NOT NULL, amount INTEGER);
INSERT INTO usage VALUES (1,'P7','2026-01-01 08:00:00',2),(2,'P7','2026-01-01 08:00:00',3),(3,'P7','2026-01-03 08:00:00',7),(4,'P8','2026-01-01 08:00:00',10);

SELECT event_id, asset_id,
       SUM(amount) OVER (
         PARTITION BY asset_id ORDER BY measured_at, event_id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS recent_total
FROM usage ORDER BY asset_id, measured_at, event_id;

ROWS counts physical rows. GROUPS counts peer groups defined by the ordering values. RANGE defines a value-based boundary, with engine-specific rules for its ordering expression and offsets.

If the task asks for the preceding 24 hours, sparse measurements make a fixed row count unsuitable. Use an explicitly defined time-range calculation and verify its boundary and timestamp representation. In SQLite, a numeric timestamp expression can support suitable RANGE offsets; it should not be improvised by subtracting from arbitrary text.

The next date lesson makes missing calendar days and half-open time boundaries visible.

LAST_VALUE returns the last value inside its frame, which is not necessarily the last row of the entire partition. Under a cumulative default, the frame often ends at the current peer group.

For the last value over the complete ordered partition, specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING with a deterministic order. By contrast, LAG and LEAD use relative row positions and do not use the frame to limit their lookup in SQLite.

A function’s name does not override its documented frame behaviour.

Move the third P7 observation a week later. A two-observation calculation should still combine it with the second observation. Set both amounts in one frame to NULL and predict an unknown total.

These tests separate what the frame counts from what SUM does with values inside it. When the output is wrong, inspect the ordered rows and frame membership before changing the arithmetic.

The workshop’s preceding-row count is adjustable, but it always counts observations rather than elapsed time.