Balances

The crosschain.assets.fungible_balances_evm model offers a historical record of fungible asset balances, including native gas tokens and ERC-20 compatible tokens.

Each entry in the model corresponds to a specific event/transaction that causes a balance change in a particular asset at the block level.

This means a new data entry is only created when a transaction alters an asset’s balance at the block level.

For example, if a wallet receives 1 ETH in 2015 and experiences no further changes until yesterday, the model will have two entries: one for the initial receipt in 2015 and another for the recent change

Blockchain Coverage

EVM-compatible blockchains supported:

ethereum, avalanche, arbitrum, base, bsc (Binance Smart Chain), polygon, optimism.

For information on Solana balances, refer to the Solana-specificdocs.

Sample Query

Generate Daily Balances from block-level balances data

[1] Replace the wallet CTE with the desired list of wallet addresses (comma-separated) or the table of wallet addresses

[2] Replace the ‘day’ argument in date_trunc function to adjust the granularity of the balance output (day, week, month, year).

with

    wallets as (

        select distinct value::varchar as wallet_address

        -- Comma-separated wallet addresses

        from

            (select split(replace(lower('0x915867061ea708869e34819216ae726041f48739, 0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037'), ' ', ''), ',') as addresses),

            lateral flatten(input => addresses)

    ),

    balances as (

        select

            date_trunc('day', b.block_timestamp) as date,

            b.chain,

            b.block_number,

            b.address,

            b.raw_balance,

            b.usd_balance,

            b.balance,

            b.token_address,

            b.token_name,

            b.token_symbol

        from crosschain.assets.fungible_balances_evm b

        inner join wallets on b.address = wallets.wallet_address

        -- where b.usd_balance is not null -- Optional filter to remove tokens without price data (mainly spam tokens)

        qualify row_number() over (partition by b.chain, b.address, b.token_address, date order by b.block_number desc) = 1

    ),

    distinct_address_tokens as (  -- Generate a list of chain x address x token

        select distinct chain, address, token_address from balances

    ),

    distinct_dates as (  -- Generate a list of timestamps (Ethereum as it is the first EVM chain)

        select date_trunc('day', timestamp) as date, count(1) as count_all

        from ethereum.raw.blocks

        where timestamp >= (select min(date) from balances)

        group by all

    ),

    date_address_token_cte as (  -- Generate timestamp x chain x address x tokens

        select t1.date, t2.chain, t2.address, t2.token_address from distinct_dates t1, distinct_address_tokens t2

    ),

    final as ( -- Forward fill the token_address, token_name, token_symbol, usd_balance, raw_balance, balance

        select

            t1.chain,

            t1.date,

            t1.address,

            lag(t2.token_address) ignore nulls over (partition by t1.chain, t1.address, t1.token_address order by t1.date) as token_address,

            lag(t2.token_name) ignore nulls over (partition by t1.chain, t1.address, t1.token_address order by t1.date) as token_name,

            lag(t2.token_symbol) ignore nulls over (partition by t1.chain, t1.address, t1.token_address order by t1.date) as token_symbol,

            lag(t2.usd_balance) ignore nulls over (partition by t1.chain, t1.address, t1.token_address order by t1.date) as usd_balance,

            lag(t2.raw_balance) ignore nulls over (partition by t1.chain, t1.address, t1.token_address order by t1.date) as raw_balance,

            lag(t2.balance) ignore nulls over (partition by t1.chain, t1.address, t1.token_address order by t1.date) as balance

        from date_address_token_cte t1

        left join balances t2 on t1.date = t2.date and t1.chain = t2.chain and t1.address = t2.address and t1.token_address = t2.token_address

        where 1 = 1

    )

select

    f.chain,

    f.date,

    f.address,

    f.token_address,

    f.token_name,

    f.token_symbol,

    f.raw_balance,

    f.balance,

    hourly.price as usd_exchange_rate,

    coalesce(hourly.price * f.balance, hourly.price * f.raw_balance / pow(10, hourly.decimals), f.usd_balance) as usd_balance

from final f

left join common.prices.hourly on f.date = hourly.timestamp and f.chain = hourly.chain and f.token_address = hourly.address

where f.raw_balance > 0

order by 1 desc

Note: the final output will contain all tokens held by the wallet, including undesirable spam tokens.

One approach to counter this would be to filter the output of the query with the desired tokens or balances above a certain USD value.

Table Column

Unique Key: unique_id

Column Name	Description
`chain`	The EVM-compatible blockchain of where the assets are held.
`address`	The wallet address holding the token.
`token_address`	The contract address of the token. Native tokens are represented with zero address.
`token_name`	The name of the token.
`token_symbol`	The symbol of the token.
`raw_balance_str`	The raw balance as a string to retain precision.
`raw_balance`	The raw balance as a numeric value.
`balance_str`	The formatted balance as a string to retain precision.
`balance`	The formatted balance as a numeric value.
`usd_balance`	The balance converted to USD.
`usd_exchange_rate`	The exchange rate used for USD conversion.
`block_timestamp`	The timestamp of the block containing the balance.
`block_number`	The block number containing the balance.
`block_hash`	The hash of the block containing the balance.
`unique_id`	A unique identifier for the balance record.
`_updated_at`	The timestamp of the last update to the balance record.

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

Blockchain Coverage

Sample Query

Table Column

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

​Blockchain Coverage

​Sample Query

​Table Column

Blockchain Coverage

Sample Query

Table Column