Overview

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.

Generating Daily Balances from Block-level Balances

-- 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
    wallets AS ( 
        SELECT DISTINCT value::varchar AS wallet_address
        FROM
            (SELECT split(replace('{{wallet_addresses}}', ' ', ''), ',') AS addresses),
            LATERAL FLATTEN(input => addresses)
    ),
    tokens AS ( 
        SELECT DISTINCT value::varchar AS token_address
        FROM
            (SELECT split(replace('{{token_address}}', ' ', ''), ',') AS addresses),
            LATERAL FLATTEN(input => addresses)
    ),
    balances AS ( 
        SELECT
            date_trunc('{{time_granularity}}', b.block_timestamp) AS date,
            b.block_number,
            b.address,
            b.raw_balance,
            b.usd_balance,
            b.balance,
            b.token_address,
            b.token_name,
            b.token_symbol
        FROM {{blockchain}}.assets.{{balances_table}} b
        INNER JOIN wallets ON b.address = wallets.wallet_address
        INNER JOIN tokens ON b.token_address = tokens.token_address
        QUALIFY
            row_number() OVER (
                PARTITION BY b.address, b.token_address, date ORDER BY b.block_number DESC
            ) = 1
    ),
    distinct_address_tokens AS ( -- Generate a list of address x token
        SELECT DISTINCT address, token_address FROM balances
    ),
    distinct_dates AS ( -- Generate a list of timestamps
        SELECT date_trunc('{{time_granularity}}', timestamp) AS date, count(1) AS count_all
        FROM {{blockchain}}.raw.blocks
        WHERE timestamp >= (SELECT min(date) FROM balances)
        GROUP BY date
    ),
    date_address_token_cte AS (  -- Generate timestamp x address x tokens 
        SELECT t1.date, t2.address, t2.token_address
        FROM distinct_dates t1, distinct_address_tokens t2
    ),
    final 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 date_address_token_cte t1
        LEFT JOIN balances t2
        ON t1.date = t2.date
        AND t1.address = t2.address
        AND t1.token_address = t2.token_address
        WHERE 1 = 1
    )
SELECT 
    date,
    token_address,
    token_name,
    token_symbol,
    usd_balance,
    raw_balance,
    balance
FROM final
WHERE raw_balance > 0
ORDER BY date 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`

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

Overview

Generating Daily Balances

Table Columns

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

​Generating Daily Balances

​Table Columns

Generating Daily Balances

Table Columns