> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.
# 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.
```sql theme={null}
-- 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 |