Balances

The balances table contains block-level changes in the balances of assets across all Ethereum wallets.

This table includes token balances of Native Ether, ERC20, ERC721, and ERC1155 tokens. Includes USD value where applicable.

Generating Daily Balances

How to generate daily balances from block-level balances table.

Generate Daily Balances

Generate wallet daily (or hourly/monthly) balance holdings with query parameters on the Allium App:

Input the following query onto any https://app.allium.so/explorer worksheet:

https://app.allium.so/s/qXwVsbGB

-- Parameters to alter:
-- [1] wallet_addresses: list of wallet addresses, comma-separated
-- e.g: 0x915867061ea708869e34819216ae726041f48739, 0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
-- [2] token_address: token contract of interest, comma-separated  
-- e.g: 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 0xdac17f958d2ee523a2206206994597c13d831ec7
-- [3] time_granularity: time granularity of address: e.g. hour / day / month
-- [4] blockchain: blockchain of interest: e.g. ethereum 
-- [5] balances_table: name of balances table: e.g. erc20_balances 

with
    address_table as ( 
        select distinct value::varchar as address_filter
        from
            (select split(replace('{{wallet_addresses}}', ' ', ''), ',') as addresses),
            lateral flatten(input => addresses)
    ),
    tokens_table as ( 
        select distinct value::varchar as tokens_filter
        from
            (select split(replace('{{token_address}}', ' ', ''), ',') as addresses),
            lateral flatten(input => addresses)
    ),
    daily_balances as (  -- Select the last balance of each token for each wallet by time window
        select
            date_trunc('{{time_granularity}}', balances.block_timestamp) as date,
            balances.block_number,
            balances.address,
            balances.raw_balance,
            balances.usd_balance,
            balances.balance,
            balances.token_address,
            balances.token_name,
            balances.token_symbol
        from {{blockchain}}.assets.{{balances_table}} balances
        inner join address_table on address = address_filter
        inner join tokens_table on token_address = tokens_filter
        qualify
            row_number() over (
                partition by address, token_address, date order by block_number desc
            )
            = 1
    ),
    hoders_dates as (  -- Generate timestamp x address x tokens 
        select
            date_trunc('{{time_granularity}}', timestamp) as date,
            address,
            token_address,
            count(1) as count_all
        from {{blockchain}}.raw.blocks, daily_balances
        where timestamp >= (select min(date) from daily_balances)
        group by all
    ),
    final_balances as (
        select
            t1.date,
            t1.address,
            lag(t2.token_address) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as token_address,
            lag(t2.token_name) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as token_name,
            lag(t2.token_symbol) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as token_symbol,
            lag(t2.usd_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as usd_balance,
            lag(t2.raw_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as raw_balance,
            lag(t2.balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) as balance
        from hoders_dates t1
        left join
            daily_balances t2
            on t1.date = t2.date
            and t1.address = t2.address
            and t1.token_address = t2.token_address
        where 1 = 1
    )
select *
from final_balances
where raw_balance > 0
order by 1 desc

Table Columns

Column Name	Description	Example
address	Address of the account.	0xd387a6e4e84a6c86bd90c158c6028a58cc8ac459
token_address	Token address of the asset. Note: Native ETH does not have a contract address, but for convention, it is labelled as: 0x0000000000000000000000000000000000000000	0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
token_name	Name of the token.	USD Coin
token_symbol	Token symbol of the token.	USDC
token_id	Unique token ID of the asset. Only applicable for ERC721 and ERC1155 tokens.
raw_balance	Balance of tokens (unnormalized)	10000000
balance	Balance of this ERC20 token, normalized by the decimal points defined in the ERC20 token contract. For example, USDC has 6 decimals, so this value is the raw_balance divided by 10^6.	10
usd_balance	The amount of tokens, in $USD.	10
usd_exchange_rate	The exchange rate used to calculate the usd_value.	1
block_timestamp	The timestamp of the block that that resulted in the balance.	2022-09-15 13:51:47
block_number	The number of the block that resulted in the balance.	15539508
block_hash	The hash of the block that resulted in the balance.	0x25680259dfb40b4ee724ae22fda01a34e6d598c5f2fc25b6d871cb56c488de70
token_type	Token type of asset. Includes ETH, ERC20, ERC721 and ERC1155.	ERC20