Back to all posts
Calculating balances on Algorand

Calculating balances on Algorand

Urtho
Urtho
guides

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

Outflows:

  • 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)

Inflows:

  • 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 (
-- OUTFLOWS
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')
UNION ALL
-- INFLOWS
SELECT rcv_addr_id addr_id, round, 0, 1, 0
  FROM mainnet.txn where type_ext in ('acc_payment','acc_payment_create')
UNION ALL
-- close_to
SELECT close_to_addr_id addr_id, round, 0, 0, 1
  FROM mainnet.txn where type_ext in ('acc_close')
) SELECT
    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
  FROM tx
  GROUP BY addr_id
  HAVING
    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
    (
        SELECT
            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
        GROUP BY
            1,
            ts
        UNION ALL
        SELECT
            rcv_addr_id,
            toDate(realtime) AS ts,
            sum(CAST(amount, 'Int128')) AS dAlgo
        FROM mainnet.txn
        WHERE typeenum = 1
        GROUP BY
            1,
            ts
    )
SELECT
    addr,
    varPop(intDiv(dAlgo,10000000)) AS varPop
FROM deltas
GROUP BY addr
HAVING count() > 100
ORDER BY 2 DESC
LIMIT 5

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 (
  SELECT
    snd_addr_id addr_id
    , uniq(rcv_addr_id) ur
    , 0 us
  FROM mainnet.txn WHERE typeenum = 1 GROUP BY 1 HAVING ur > 1000
UNION ALL
  SELECT
    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 Allo.info Algorand Self Service BI offering.
Please contact [email protected] for a custom quote.

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

#guide #Algorand #sql #balances #self-service BI