Write a SQL query that returns every ledger entry with the account balance immediately after that entry.

Table: LedgerEntries(EntryId, AccountId, PostedAtUtc, EntryType, Amount), where EntryType is Credit or Debit and Amount is positive. Return a signed amount and running balance for each account.

A credit of 100 followed by a debit of 30 produces running balances 100 and 70. Accounts are calculated independently.

• Partition balances by AccountId.
• Order by timestamp and then EntryId.
• Use an explicit ROWS frame.
• Reject unknown entry types through a database constraint or upstream validation.

I first translate each row into a signed amount. The running balance is a cumulative SUM partitioned by account. Adding EntryId to the timestamp order makes simultaneous entries deterministic, and ROWS UNBOUNDED PRECEDING makes the frame operate on physical ordered rows rather than peer groups.

WITH SignedEntries AS
(
    SELECT
        e.EntryId,
        e.AccountId,
        e.PostedAtUtc,
        CASE
            WHEN e.EntryType = 'Credit' THEN e.Amount
            WHEN e.EntryType = 'Debit' THEN -e.Amount
        END AS SignedAmount
    FROM dbo.LedgerEntries AS e
)
SELECT
    EntryId,
    AccountId,
    PostedAtUtc,
    SignedAmount,
    SUM(SignedAmount) OVER
    (
        PARTITION BY AccountId
        ORDER BY PostedAtUtc, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningBalance
FROM SignedEntries
ORDER BY AccountId, PostedAtUtc, EntryId;

The window requires rows ordered per account. An index on (AccountId, PostedAtUtc, EntryId) including type and amount can reduce sorting. Large ledger exports may still process substantial history, so bounded ranges or stored opening balances can be useful.

• Two entries at the same timestamp.
• An account beginning with a debit.
• Several accounts interleaved in storage.
• A zero-amount entry if the model permits it.

• Shuffle insertion order while keeping posted timestamps.
• Use equal timestamps to verify the id tie-breaker.
• Verify one account never affects another account's balance.

• Forgetting to partition by account.
• Ordering only by a non-unique timestamp.
• Using the default range frame without understanding peer behavior.
• Storing debits inconsistently as both negative amounts and a debit type.