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 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:

-- 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 NameDescriptionExample

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

Last updated