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