Balances

The balances table contains transaction_block-level changes in the balances of coins across all Sui wallets. Includes USD value where applicable.

This table is built by calculating the cumulative sum of amounts in the balance_change table. Note: Failed transaction_blocks are factored in the total balance. These balance changes are spent gas fees.

Generating Daily Balances

How to generate daily balances from transaction_block-level balances table.

Generating Daily Balances from Transaction_Block-level Balances

-- Parameters to alter:
-- [1] wallet_addresses: list of wallet addresses, comma-separated
-- e.g: 0x1d632d46ff70491033fefc4e6398dceaa4943dcf62512b4d57378b5ab703bc5e, 0x2d632d46ff70491033fefc4e6398dceaa4943dcf62512b4d57378b5ab703bc5e
-- [2] coin_types: coin types of interest, comma-separated  
-- e.g: 0x2::sui::SUI, 0x2::usdc::USDC
-- [3] time_granularity: time granularity of address: e.g. hour / day / month

WITH
    wallets AS ( 
        SELECT DISTINCT value::varchar AS wallet_address
        FROM
            (SELECT split(replace('{{wallet_addresses}}', ' ', ''), ',') AS addresses),
            LATERAL FLATTEN(input => addresses)
    ),
    coins AS ( 
        SELECT DISTINCT value::varchar AS coin_type
        FROM
            (SELECT split(replace('{{coin_types}}', ' ', ''), ',') AS addresses),
            LATERAL FLATTEN(input => addresses)
    ),
    balances AS ( 
        SELECT
            date_trunc('{{time_granularity}}', b.checkpoint_timestamp) AS date,
            b.checkpoint_sequence,
            b.address,
            b.raw_balance,
            b.usd_balance,
            b.balance,
            b.coin_type,
            b.coin_name,
            b.coin_symbol
        FROM sui.assets.balances b
        INNER JOIN wallets ON b.address = wallets.wallet_address
        INNER JOIN coins ON b.coin_type = coins.coin_type
        QUALIFY
            row_number() OVER (
                PARTITION BY b.address, b.coin_type, date ORDER BY b.checkpoint_sequence DESC
            ) = 1
    ),
    distinct_address_tokens AS ( -- Generate a list of address x token
        SELECT DISTINCT address, coin_type FROM balances
    ),
    distinct_dates AS ( -- Generate a list of timestamps
        SELECT date_trunc('{{time_granularity}}', timestamp) AS date, count(1) AS count_all
        FROM sui.raw.checkpoints
        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.coin_type
        FROM distinct_dates t1, distinct_address_tokens t2
    ),
    final AS (
        SELECT
            t1.date,
            t1.address,
            lag(t2.coin_type) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS coin_type,
            lag(t2.coin_name) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS coin_name,
            lag(t2.coin_symbol) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS coin_symbol,
            lag(t2.usd_balance) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS usd_balance,
            lag(t2.raw_balance) ignore nulls over (partition by t1.address, t1.coin_type order by t1.date) AS raw_balance,
            lag(t2.balance) ignore nulls over (partition by t1.address, t1.coin_type 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.coin_type = t2.coin_type
        WHERE 1 = 1
    )
SELECT 
    date,
    address,
    coin_type,
    coin_name,
    coin_symbol,
    usd_balance,
    raw_balance,
    balance
FROM final
WHERE raw_balance > 0
ORDER BY date DESC, address, coin_type

Table Columns

Column Name	Description	Example
address	Address of the account	0x1d632d46ff70491033fefc4e6398dceaa4943dcf62512b4d57378b5ab703bc5e
coin_type	Type of the coin	0x2::sui::SUI
coin_name	Name of the coin	Sui
coin_symbol	Symbol of the coin	SUI
raw_balance	Balance of tokens (unnormalized)	9996684712015
raw_balance_str	String representation of raw balance	9996684712015
balance_str	String representation of normalized balance	9996.684712015
balance	Normalized balance of the token	9996.684712015
usd_balance	The amount of tokens in USD at the current time	5127.591491986
usd_exchange_rate	The exchange rate used to calculate the USD value	0.5129292
checkpoint_timestamp	The timestamp of the checkpoint	2023-11-07 14:57:27.169
checkpoint_sequence	The sequence number of the checkpoint	17645270
checkpoint_digest	The digest of the checkpoint	CHRApjya4BKfCZ5fz92DvQ4QZ8CUpErALqsw8QXKh3ar
transaction_block_digest	The digest of the transaction block	EVSE1tStdEFxe7dt1Fd7wbqK4y7RFrcw1gResBxqdgoG
transaction_block_index	The index of the transaction block	56
unique_id	Unique identifier for the balance entry	checkpoint-17645270_transaction-EVSE1tStdEFxe7dt1Fd7wbqK4y7RFrcw1gResBxqdgoG_address-0x1d632d46ff70491033fefc4e6398dceaa4943dcf62512b4d57378b5ab703bc5e_coin_type-0x2::sui::SUI
_created_at	Timestamp when the record was created	2025-06-03 16:45:31.319
_updated_at	Timestamp when the record was last updated	2025-06-03 16:45:31.319

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

Generating Daily Balances

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

​Generating Daily Balances

Generating Daily Balances