Token Balances

The tokens_<chain>.balances_daily table contains all daily token balances across all addresses and on EVM-compatible networks. This dataset encompasses:

• Native currency balances (such as ETH, MATIC)
• ERC-20 token balances
• ERC-721 (NFT) balances
• ERC-1155 token balances

The table contains the following columns:

​

Utility

Leveraging the balance tables equips analysts and developers with a foundational dataset for diverse applications, enabling you to:

• Evaluate token distribution patterns
• Identify leading token holders
• Examine an address’s token portfolio
• Review multi-signature wallet holdings

and much more, offering a granular view into the dynamics of token economics.

​

Network Coverage

Balances are available for the following EVM-compatible networks:

• Arbitrum
• Avalanche C-Chain
• Base
• Ethereum Mainnet
• Gnosis Chain
• Optimism
• Scroll
• ZkSync

​

Balance Calculation Methodology

Our approach to storing balances is the following: We store token balances for each address. This process involves querying the blockchain for the balance of each address at the end of the day. The balance is then stored in the tokens_<chain>.balances table, which is updated . For the balance to be queried, the address must have appeared in any of the following tables:

• For native currencies: Transactions and traces to/from addresses, Ethereum-specific withdrawals, and block mining/validator activities.
• For ERC20: Analysis is based on token transfer events detailed in the tokens_ethereum_base_transfers. The address balance is updated based on the to/from addresses in the transfer events. If the token is a non-standard ERC20 token, the balance might not be updated until we detect a standard ERC20 transfer event. Known issues currently include ERC4626 tokens and rebasing tokens like stETH or $ampleforth. We are working on improving the range of balance changes that we detect.
• For ERC721 and ERC1155: Inspection of NFT transfer activities via the nft_ethereum_transfers, focusing on the to/from addresses.

These dependencies on transfer and NFT event tables introduce a delay to balance updates, contingent on the latency inherent in the Spellbook’s processing.

This also means that if an address has not been involved in any of the above activities, its balance will not be updated. This might be the case for non-standard erc20 contracts that do not emit the Transfer event, or for addresses that have not been involved in any transactions or NFT transfers. If there is a specific token that does not emit the Transfer event, but has a different event for token transfers, please let us know and we will see if we can include it in our balance calculations.

​

Granular balances

We also provide a more granular view of token balances in the tokens_<chain>.balances table, which contains granular balance changes for each address, token combination per block. This table is updated in near real-time and is used to calculate the daily balances. This table is useful for more granular analysis, such as tracking the balance changes of a specific address over time.

However, the tokens_<chain>.balances table is very large and is not suitable for querying large time ranges or for querying balances for many addresses at once. For these use cases, the tokens_<chain>.balances_daily table is more suitable.

The tokens_<chain>.balances table adds a new row every time a balance changes but does not carry the balance forward for every block. Instead, it only creates a new row for a balance change of an address and token combination.

​

Table Sample

​

Sample Queries

​

Querying for the latest fungible Token Balances for a specific Address

This query will return the latest fungible token balances for the specified address. Fungible tokens refer to erc20 and native tokens. Please keep in mind that the balance table lags behind the real state by approximately 2 hours.

select 
    address
    ,token_symbol as symbol
    ,balance
    ,balance_usd
    ,token_address
from tokens_{{blockchain}}.balances_daily
where address = {{wallet_address}} and day = date_trunc('day', now())
    and (token_standard = 'erc20' or token_standard = 'native')
    and balance_usd > 1
order by balance_usd desc

​

Querying for daily fungible Token Balances for a specific Address

This query will return the daily fungible token balances for the specified address over time. Fungible tokens refer to erc20 and native tokens.

select
     b.day
    ,b.token_symbol
    ,b.token_address
    ,b.balance
    ,b.balance_usd
from tokens_{{blockchain}}.balances_daily  b
where address = {{wallet_address}} 
    and (token_standard = 'erc20' or token_standard = 'native')
    and b.day > date_trunc('day',now()) - interval '{{months}}' month
    and balance_usd > 1
order by day desc, balance_usd desc

​

Find the top 50 token holders for a specific token

This query will return the top 50 token holders for a specific ERC20 token on the Ethereum Mainnet.

WITH RankedBalances AS (
    SELECT
        b.address,
        b.balance,
        ROW_NUMBER() OVER(PARTITION BY b.day ORDER BY b.balance DESC) AS rank
    FROM tokens_ethereum.balances_daily b
    WHERE token_address = 0xbaac2b4491727d78d2b78815144570b9f2fe8899
    AND b.day = date_trunc('day', current_date)
)
SELECT
    address,
    balance
FROM RankedBalances
WHERE rank <= 50;

https://dune.com/embeds/3503852/5892563/