Find a way to report the balance only after all postings of a transaction are applied

[dnicolodi]

Given a ledger like

plugin "beancount.plugins.auto_accounts"

2024-06-16 * "Example"
  Assets:Foo                                        -1000.00 EUR
  Assets:Foo                                         
  Expenses:Bar                                          2.00 EUR

2024-06-17 * "Example"
  Assets:Foo                                           -1.00 EUR
  Expenses:Bar

trying to compute the maximum balance of the Assets:Foo account with a query like

SELECT date, balance WHERE account = 'Assets:Foo' ORDER BY balance ASC LIMIT 1

results in

   date       balance
──────────  ────────────
2024-06-17  -1000.00 EUR

which is likely not the desired result.

This happens because each posting is considered in isolation. This can be solved finding a way to report the balance only after all the postings relative to a transaction have been applied.

Making balance() a function similar to a windowed aggregate function, this could look something like:

SELECT 
  date, 
  balance(amount) AS balance
FROM (
  SELECT 
    date, 
    sum(position) AS amount
  FROM
    postings 
  GROUP BY 
    id
  )
ORDER BY
  balance 
LIMIT 1