Calculating balances on Algorand

Calculating balances on Algorand


Account balance on Algorand

Algorand blockchain keeps both transaction (block) history as well as the current account state for all non-zero balance addresses.

Getting the balance of tokens on an account is just a single call/lookup on the Algorand Indexer SQL schema or native node API. But what if we are interested in historical data?

How quickly can we get data that shows

  • balance history for an account
  • token distribution amongst holders at some round/block
  • a snapshot of account balance at some point in the past

We get there by going through all transactions on the accounts up to the specified point in time and adding all token inflows and outflows.

Limited “time-travel” functionality is provided by Algorand Indexer API but does not work for accounts with any kind of smart contract activity on them.

Accounting events that affect balance

Let’s find out what inflows and outflows are possible on Algorand


  • native payment or ASA token transfer
  • transaction fee (if covered by the account)
  • account close (native or ASA)
  • token clawback
  • token destroy (creator account only)


  • native payment or ASA token transfer
  • receiving end of account close operation (native or ASA)
  • rewards (native token only)
  • token clawback
  • token mint

Notice that the transaction fee might be covered by another entity (eg smart contract) when the transaction is part of a group.

Rewards are no longer live on Algorand and as optimization are stored separately from TX history in Allo DW schema.

This article will focus on native tokens only. Queries that work with ASA are left as an exercise for the reader. :)

Finding an interesting account

We are interested in an account that had all types of transactions (except being closed out) over a long period of time.

WITH tx AS (
SELECT snd_addr_id addr_id, round, 1 outflow ,0 inflow, 0 closeto
FROM mainnet.txn where type_ext in ('acc_payment','acc_payment_create')
SELECT rcv_addr_id addr_id, round, 0, 1, 0
FROM mainnet.txn where type_ext in ('acc_payment','acc_payment_create')
-- close_to
SELECT close_to_addr_id addr_id, round, 0, 0, 1
FROM mainnet.txn where type_ext in ('acc_close')
addr_id -- address id
, min(round) r1 -- first round
, max(round) r2 -- last round
, sum(outflow) outflows -- number of outflows TX
, sum(inflow) inflows -- number of inflows TX
, sum(closeto) closeto -- number of close to TX
GROUP BY addr_id
r2 - r1 > 10000000 -- spanning >10M round
AND outflows > 1000000 -- at least 1M outflows
AND inflows > 1000000 -- at least 1M inflows
AND closeto > 1 -- at least 1 closeto
ORDER BY count() DESC limit 1

This will pick the top account by the number of transactions that has at least 1M inbound and 1M outbound payment transactions spanning at least 10M rounds.

Note that Allo DW transaction type annotation differentiates between a payment transaction (‘acc_payment’) to an account that already existed and ‘acc_payment_create’ transaction that initially funded an account.

Allo DW schema stores accounts as UInt64 IDs with a separate map between IDs and full Algorand 58 character address encoding.
The one chosen is AACCDJ…LTEN4A with ID: 1740798472

Getting Algo balance history for an account

To calculate daily Algo holdings for an account we construct a union query that aggregates all daily deltas from rewards, inflows, outflows and calculate a running sum over the output.

Note that we calculate using Int128 to prevent overflow while keeping full precision in micro Algos. The result is finally shifted by 6 decimals to arrive at the Algo value.

with inoutalgoagg as (
with deltas as (
-- Add all sender rewards
select toDate(realtime) ts, sum(rs::Int128) dAlgo
from mainnet.txn_rewards
where sid = 1740798472 and rs > 0 group by ts
union all
-- Add all receiver rewards
select toDate(realtime) ts, sum(rr::Int128) dAlgo
from mainnet.txn_rewards
where rid = 1740798472 and rr > 0 group by ts
union all
-- Add all close_to rewards
select toDate(realtime) ts, sum(rc::Int128) dAlgo
from mainnet.txn_rewards
where cid = 1740798472 and rc > 0 group by ts
union all
-- All fees :: outflows (minus)
select toDate(realtime) ts, -sum(fee::Int128) dAlgo from mainnet.txn
where snd_addr_id = 1740798472 and fee > 0 group by ts
-- PAYments from the account :: outflows (minus)
-- lets not forget about the outflow in case of account close
union all
select toDate(realtime) ts, -sum(amount::Int128 + close_amount::Int128) dAlgo
from mainnet.txn
where snd_addr_id = 1740798472 and typeenum = 1 group by ts
-- PAYments to the account :: inflows (in plus)
union all
select toDate(realtime) ts, sum(amount::Int128 ) dAlgo from mainnet.txn
where rcv_addr_id = 1740798472 and typeenum = 1 group by ts
-- PAYmets to the account that are part of close :: inflows (in plus)
union all
select toDate(realtime) ts, sum(close_amount::Int128) dAlgo from mainnet.txn
where close_to_addr_id = 1740798472 and typeenum = 1 group by ts
) select ts, sum(dAlgo) da, sumState(dAlgo) ra from deltas group by ts order by ts
) select ts, da, runningAccumulate(ra)/1000000 balance from inoutalgoagg

This method ignores the initial balance of genesis accounts. One needs to add genesis data stored separately in Allo DW to get the correct results for this particular set of accounts.

Finding an interesting account … take 2

This time let’s find an account with the highest daily delta variance and over a minimum of 100-day time span.

WITH deltas AS
snd_addr_id AS addr,
toDate(realtime) AS ts,
-sum(CAST(amount, 'Int128') + CAST(close_amount, 'Int128')) AS dAlgo
FROM mainnet.txn
WHERE typeenum = 1
toDate(realtime) AS ts,
sum(CAST(amount, 'Int128')) AS dAlgo
FROM mainnet.txn
WHERE typeenum = 1
varPop(intDiv(dAlgo,10000000)) AS varPop
FROM deltas
HAVING count() > 100

Crunching quick daily balance deltas across 1.1 billion transactions takes less than 10 seconds with the following results:

Let’s graph the balance of V3ZJHY…3P5IIA :

Take 3

This time let’s find the most popular account where the popularity metric is going to be defined as a product of unique inflow addresses and unique outflow addresses.

WITH unique_senders_receivers as (
snd_addr_id addr_id
, uniq(rcv_addr_id) ur
, 0 us
FROM mainnet.txn WHERE typeenum = 1 GROUP BY 1 HAVING ur > 1000
rcv_addr_id addr_id
, 0 ur
, uniq(snd_addr_id) us
FROM mainnet.txn WHERE typeenum = 1 GROUP BY 1 having us > 1000
) SELECT addr_id, sum(us)*sum(ur) popularity
FROM unique_senders_receivers GROUP BY 1 ORDER BY 2 DESC limit 5

The popularity contest winner is … BINANCE with account id 3907164
address SP745J…MDCZVM

The same principle can be applied to calculate ASA token or online stake distribution at specific block but we’ll explore this idea in another article.

A single pass (no unions) query is possible using ClickHouse dynamic arrays but would be harder to deconstruct in the article. Also the union version already executes in under 15 seconds over all accounts across over a billion of transactions.

All examples use commercial Algorand Self Service BI offering.
Please contact [email protected] for a custom quote.

🔬_Note that researchers get free access to the platform._ 🔬