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