Articles
Running Balance SQL Interview Questions: Tied Timestamps, Reversals, and Opening Balances
Solve running-balance SQL with explicit frames, stable tie order, opening anchors, reversals, and account-level reconciliation checks.
Two ledger entries share the same timestamp: a debit of 30 and a credit of 50. Starting from 100, should the displayed balances be 70 and 120, or 150 and 120? Both sequences end at the same total. Only the ledger's ordering contract tells you which intermediate balances are meaningful.
A running balance is an opening anchor plus an ordered accumulation of signed entries. SQL window syntax is one part of that definition. You also need account boundaries, a stable order, a frame, a reporting cutoff, and a policy for reversals and late records.
This guide uses an original ledger exercise. Official PostgreSQL documentation supports the window and numeric semantics; modeling choices and interview advice are editorial reasoning, not employer or candidate claims. For adjacent practice, see Write SQL Data Transformation Queries.
Specify what the balance represents
Assume a table entries(entry_id, account_id, currency, posted_at, amount, reversal_of) and a table opening_balances(account_id, currency, period_start, opening_balance). Amounts are signed: positive values increase this exercise's balance and negative values decrease it. That convention is declared here; it is not a universal accounting sign convention.
The opening table contains exactly one row per account, currency, and reporting start. Each opening amount represents the balance immediately before January 1, 2026. Current-period entries start at that timestamp and stop before February 1. Every entry has a unique ID, a non-NULL amount, and a non-NULL timestamp.
The requested output is one row per posted entry, not one row per day and not a complete account statement with opening-only rows. Accounts without period activity therefore produce no entry rows, though they still have an opening balance. Mention that output limitation rather than claiming the query lists every account.
For this fixture, (posted_at, entry_id) defines the statement order. The ID is an agreed final tie-breaker. If the source has an authoritative ledger sequence, use that instead. A random UUID can provide deterministic ordering without representing the true business sequence.
Calculate the expected rows by hand
Account A has a January opening balance of 100 USD. Account B opens with 20 USD. A pre-January credit of 10 for A is already included in its opening anchor and must not be added again.
| Entry | Account | Posted time | Signed amount | Meaning | Expected balance |
|---|---|---|---|---|---|
| 101 | A | Jan 2, 09:00 | −30 | Original debit | 70 |
| 102 | A | Jan 2, 09:00 | +50 | Credit at the same timestamp | 120 |
| 103 | A | Jan 3, 10:00 | +30 | Reversal of entry 101 | 150 |
| 201 | B | Jan 2, 09:00 | −5 | Separate account debit | 15 |
The final balance for A is 100 - 30 + 50 + 30 = 150. The reversal cancels entry 101's amount in the ending total, but it does not erase the fact that the debit appeared earlier in the statement. Account B's balance must not depend on any A entry.
This small fixture tests ties, partitions, opening inclusion, and reversal timing. If a proposed query cannot explain all four rows, optimization is premature.
Write an explicit row-by-row window
WITH period_entries AS (
SELECT entry_id, account_id, currency,
posted_at, amount, reversal_of
FROM entries
WHERE posted_at >= TIMESTAMP '2026-01-01 00:00:00'
AND posted_at < TIMESTAMP '2026-02-01 00:00:00'
)
SELECT e.account_id, e.currency, e.entry_id,
e.posted_at, e.amount, e.reversal_of,
o.opening_balance
+ SUM(e.amount) OVER (
PARTITION BY e.account_id, e.currency
ORDER BY e.posted_at, e.entry_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance
FROM period_entries AS e
JOIN opening_balances AS o
ON o.account_id = e.account_id
AND o.currency = e.currency
AND o.period_start = DATE '2026-01-01'
ORDER BY e.account_id, e.currency, e.posted_at, e.entry_id;
The partition isolates each account–currency ledger. The order determines the accumulation sequence. The explicit ROWS frame includes entries from the beginning of that partition through the current ordered row. The outer ORDER BY determines presentation; it does not replace the order inside the window.
Official PostgreSQL semantics: ordered window aggregates operate over their window frame, and frame choice can change running results. See window functions and window expression syntax.
The complete query was executed in DuckDB 1.5.2 and returned A's balances 70, 120, and 150, plus B's 15. This is a correctness check on the stated fixture, not a PostgreSQL performance measurement.
The inner join assumes all relevant opening anchors exist and are unique. In production, validate that assumption before running the statement. An absent anchor must not silently remove an account, and a duplicated anchor must not multiply its entries.
Tied timestamps expose the frame contract
With only ORDER BY posted_at, PostgreSQL's default ordered frame includes peers through the current timestamp. In the fixture, entries 101 and 102 are peers. A timestamp-only default-frame calculation can give both rows the balance 120 because both amounts are included together.
That may be appropriate for a balance after all activity at a timestamp. It is not the requested entry-by-entry statement. Official syntax documentation describes the default RANGE frame and peer behavior; it also distinguishes ROWS, RANGE, and GROUPS modes.
Changing only the frame to ROWS is still insufficient if tied rows have no stable ordering rule. One entry may be processed before the other without a reproducible business reason. Our query therefore specifies both the frame and a complete order.
If the task asks for end-of-day balances, aggregate signed movements by account, currency, and day first. Then accumulate those daily amounts. The resulting rows answer a different question from the entry-level statement, and the daily ordering no longer needs to choose between entries within the same day.
Keep the opening anchor aligned with the filter
Filtering entries to January removes earlier history from the window. That is correct only because the opening anchor already summarizes that earlier history. If you start the sum at zero, A's first balance becomes −30 instead of 70.
There are two valid broad strategies. Compute balances over complete history and filter the displayed period afterward, or use a verified opening snapshot and sum only subsequent movements. Do not combine a snapshot that already includes an entry with a movement range that includes the same entry again.
If deriving the opening from history, use entries strictly before the period start. The first current-period entry is included at or after the start. A precise boundary avoids both gaps and double counting. When the snapshot is sequence-based, use a compatible sequence cutoff rather than assuming its timestamp describes the exact included set.
For a midperiod report beginning January 3, either carry forward the balance immediately before that day or compute the earlier period balances before filtering. Reusing the January 1 opening while summing only January 3 entries would omit January 2's net movement of 20.
The opening is not just another number to add. It is a statement about which historical entries have already been included.
Treat reversals as entries with an explicit relationship
Our full reversal adds +30 at entry 103 and references the original −30 entry 101. The statement shows 70 after the debit, 120 after the separate credit, and 150 after the later reversal. Deleting entry 101 would rewrite those earlier balances and remove evidence of the original event.
This append-style model is an editorial design choice for the exercise. A system may also maintain materialized views, correction versions, or other representations, but its query must explain which history is being reported.
Validate the reversal relationship independently from the running sum. A syntactically correct window will happily include two accidental full reversals, a reversal against another account, or a reversal in a different currency. For a full reversal, check the target, amount sign and magnitude, and permitted multiplicity under the system's contract.
Partial reversals need a different rule: several adjustments might legitimately reference one original entry while their cumulative amount remains within an allowed limit. A blanket uniqueness constraint on reversal_of would reject that model. State whether the interview exercise allows full, partial, or repeated adjustments before proposing validation.
Do not infer a reversal merely because two unrelated entries have opposite equal amounts. The reference or business event identity supplies the relationship; arithmetic coincidence does not.
Late entries can change a historical answer
Suppose an entry arrives today with an effective date last week. A balance ordered by effective time may change previously displayed historical rows. A balance ordered by posting sequence answers when the ledger recorded movements instead. Those are different timelines.
Name the intended one. If the report must reproduce what was known at an earlier cutoff, retain an ingestion or recorded timestamp and evaluate against that knowledge boundary. Rerunning against today's corrected table without a version or cutoff may produce a valid current reconstruction but not the old published statement.
Opening snapshots must follow the same rule. A snapshot built before a late historical entry may no longer reconcile with an effective-time reconstruction unless the correction is incorporated deliberately. Version the anchor or account for the late adjustment; do not silently mix incompatible snapshots and movements.
This is why the query returns entry IDs and reversal references alongside amounts. A reported difference should be traceable to contributing records, not only visible as a changed total.
Choose exact units and preserve currency boundaries
Official PostgreSQL documentation describes numeric as an exact numeric type and floating-point types as inexact. See numeric types. For this example, decimal amounts avoid presenting binary floating-point artifacts as meaningful balance differences.
Integer minor units are another possible representation when the currency and scale are known. Check range limits and conversions; different currencies or instruments can require different scales. Do not cast every amount to whole units merely to make the sum look clean.
Partitioning by currency avoids adding USD and EUR into an unexplained total. A converted reporting balance requires explicit exchange-rate timing and conversion policy. That is a separate measure, not something the window function chooses automatically.
NULL amounts also need a data policy. Aggregates can ignore NULL inputs, which may conceal missing money values. Reject or quarantine invalid amounts rather than casually treating every NULL as zero. A missing opening anchor is similarly not proof that an account started empty.
Reconcile intermediate and ending balances
For each ordered entry after the first, the difference between consecutive running balances should equal that entry's signed amount. For the first row, subtract the opening anchor instead. The final balance should equal opening plus the sum of all eligible period movements.
The saved verification checks these equations independently in Python, tests reordered physical input, and confirms the timestamp-peer counterexample. It also checks that the pre-January credit is excluded from current movements and that account B remains isolated.
These invariants catch different defects. An ending total alone may miss an incorrect tie order because addition gives the same final sum. A row-by-row check alone may still use the wrong opening. Combining both checks makes the evidence stronger.
For a no-activity account, verify the separate statement policy. If the requested output includes all account balances, start from the account or opening population and construct an opening-only result deliberately. Do not pretend the entry-level query above already supplies one.
Explain performance after correctness
A useful access path may align with account, currency, and the ordering columns, but the actual plan depends on filters, data volume, engine, and storage layout. Inspect whether the query sorts, scans large history, or multiplies rows through dimension joins.
Snapshots can reduce historical work, but they introduce a maintenance and reconciliation obligation. Explain the included-entry boundary, refresh policy, and handling of corrections before recommending them as a shortcut.
In a timed interview, prioritize the contract, complete query, and fixture. A clear explanation of why tied entries receive 70 and 120 is more valuable than an unsupported claim that the solution is “linear time” because it uses one SUM expression.
Practice ordered accumulation and state reasoning
| Question | Practice focus |
|---|---|
| Write SQL Data Transformation Queries | Aggregate daily values before monthly-reset cumulative totals |
| Implement a balance tracker and interval merger | Explain balance state and update invariants |
| Solve window-function SQL without joins | Choose partitions, ordering, and frames explicitly |
| Write SQL revenue and anomaly queries | Reconcile aggregate results with source movements |
| Reason About Composite Join Keys and Predicate Placement | Preserve account and currency scope through joins |
Start with Write SQL Data Transformation Queries, then explain how its monthly reset differs from carrying an account's opening balance into a new statement period.