SQL running totals: choose the correct window frame

A running total depends on both ordering and the window frame: the set of rows included for each result. A row-by-row cumulative amount is different from a cumulative amount that includes every peer sharing the current date.

This difference becomes visible when multiple transactions have the same ordering value. If you leave the frame implicit, the engine's default may group those peers in a way you did not expect.

The examples below execute in SQLite using the advanced SQL lab. They use invented ledger entries so the sums are easy to inspect.

Compare the same data under two frames

WITH ledger(entry_id, business_day, amount) AS (
    VALUES ('A','2026-01-01',100),
           ('B','2026-01-01',200),
           ('C','2026-01-02',50)
)
SELECT entry_id, business_day, amount,
    SUM(amount) OVER (ORDER BY business_day) AS default_total,
    SUM(amount) OVER (
        ORDER BY business_day, entry_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS row_total
FROM ledger
ORDER BY business_day, entry_id;

In SQLite, the default cumulative frame with this ORDER BY includes the current row's peers. The first two rows therefore both show 300 in default_total. The explicit row-based total shows 100, 300 and 350.

Neither total is automatically wrong. The default result reflects the cumulative value through the current date, including all entries tied on that date. The explicit ROWS result reflects a stable sequence of individual entries.

SQLite documents the default frame as RANGE from the start of the partition through the current row and its peers. Other engines should be checked rather than assumed to behave identically. SQLite window frames.

Decide whether the report is transactional or daily

For a transaction ledger, a deterministic sequence may be required. Use an authoritative event timestamp and a stable tie-breaker if timestamps collide. An arbitrary ID can make the result reproducible, but it does not establish the true business order of simultaneous events.

For a daily management report, aggregate to one row per day first:

WITH ledger(entry_id, business_day, amount) AS (
    VALUES ('A','2026-01-01',100),
           ('B','2026-01-01',200),
           ('C','2026-01-02',50)
), daily AS (
    SELECT business_day, SUM(amount) AS daily_amount
    FROM ledger
    GROUP BY business_day
)
SELECT business_day, daily_amount,
    SUM(daily_amount) OVER (
        ORDER BY business_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM daily
ORDER BY business_day;

The output has two rows: January 1 with daily/cumulative values 300/300, and January 2 with 50/350. The result grain now matches the daily question, so a reviewer does not need to interpret repeated cumulative values across transaction rows.

Use PARTITION BY to define independent balances

A customer-level running spend should restart for each customer. An account balance should not mix transactions from different accounts. Add the appropriate partition key and verify that it represents the entity whose history is being accumulated.

If you also partition by month, the total restarts every month. That may be right for month-to-date spend but wrong for a lifetime balance. Write the restart rule in the metric definition rather than leaving it implied by the SQL.

The same caution applies to filtering. If you filter source rows to April before calculating a running total, the window cannot include January–March activity. For a lifetime balance displayed only in April, calculate over the full required history, then filter the result in an outer query.

Missing dates do not appear automatically

A cumulative total can skip dates with no records. That is often acceptable for a transaction view, but a daily dashboard may require a continuous calendar. Join to a calendar and decide whether absence means zero activity or missing source data before filling gaps.

For a moving average, the distinction becomes especially important: seven rows are not necessarily seven calendar days. A window over sparse activity dates answers a different question from a seven-day window.

Keep the frame type, date completeness and input grain together in the specification. A technically correct window cannot infer whether missing dates represent inactivity or incomplete ingestion.

Reconcile the final cumulative value

The last cumulative amount within a complete partition should equal the ordinary sum of the same eligible inputs. Also inspect intermediate values around ties and boundaries. A final total can be correct even when the row-by-row presentation is misleading.

Exercise: add a second January 2 entry worth -30. Compare transaction and daily views. The final total becomes 320, but the intermediate sequence depends on the ordering convention. Explain which view a stakeholder needs before choosing the SQL.

For the broader validation approach, see five SQL checks. NeuraPath's Data Analytics with Generative AI course connects window functions with the business definitions needed to use them reliably in reports and pipelines.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in ROW_NUMBER versus RANK versus DENSE_RANK on tied sales.
• Continue with LAG and LEAD for repeat-purchase intervals.