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.

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