Balances Latest - Allium Documentation Hub

The balances latest table contains the latest balances of every type of token of any address. This includes Native Ether, ERC20, ERC721, and ERC1155 tokens. For the value of the asset balances, there are two USD balance fields:

usd_balance_current which reflects the current USD value of the asset.
usd_balance_at_block_timestamp which reflects the USD value of the asset at the last transaction activity.

Table Details

Property	Value
Table Name	`ethereum.assets.balances_latest`
Table Status	Production-Ready
Unique Key	`token_address`, `address`

Table Columns

Column Name	Data Type	Description
token_type	VARCHAR(16777216)	Token standard for the asset. One of: `erc20` (fungible), `erc721` (non-fungible), `erc1155` (semi-fungible), or the native currency symbol (e.g. `eth`, `matic`, `bnb`).
address	VARCHAR(42)	Address of the account.
token_address	VARCHAR(42)	Address of the token.For Ether it is represented as nulll address.
token_name	VARCHAR(16777216)	Name of the token.
token_symbol	VARCHAR(16777216)	Symbol of the token.
token_id	VARCHAR(16777216)	Token ID of a ERC721 token or ERC1155 token.
raw_balance	FLOAT	Raw balance amount of the token (not divided by the number of decimals)
raw_balance_str	VARCHAR(16777216)	Raw balance amount of the token (not divided by the number of decimals) in string.
balance_str	VARCHAR(16777216)	Balance of token normalized and in string format.
balance	FLOAT	`raw_balance` divided by the number of decimals of this token. This value will be the same as raw_balance for erc721 and erc1155 tokens.
usd_balance_current	FLOAT	USD balance of token at the current timestamp.
usd_balance_at_block_timestamp	FLOAT	USD balance of token at the timestamp of the last activity that resulted in balance change.
usd_exchange_rate_current	FLOAT	USD exchange rate of token at the current timestamp.
usd_exchange_rate_at_block_timestamp	FLOAT	USD exchange rate of token at the timestamp of the last activity that resulted in balance update.
last_activity_block_timestamp	TIMESTAMP_NTZ(9)	The last timestamp of the block that that resulted in the balance update.
last_activity_block_number	NUMBER(38,0)	The last block number that resulted in the balance update.
last_activity_block_hash	VARCHAR(66)	The block hash of the last activity that resulted in this balance.
unique_id	VARCHAR(16777216)	Unique ID of the balance.
_created_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was created in the database.
_updated_at	TIMESTAMP_NTZ(9)	Timestamp of the balance update.

Sample Query

Getting the latest balances address.

select * from ethereum.assets.balances_latest
where address = '0xd387a6e4e84a6c86bd90c158c6028a58cc8ac459'

Finding the distribution of current ETH holdings amongst users who bought an NFT on OpenSea in the last 7 days.

select 
  case 
  when balance <= 0.1 then 'a. >0-0.1 ETH'
  when balance > 0.1 and balance <= 1 then 'b. >0.1-1 ETH'
  when balance > 1 and balance <= 10 then 'c. >1-10 ETH'
  when balance > 10 and balance <= 100 then 'd. >10-100 ETH'
  when balance > 100 then 'e. >100 ETH' end as eth_holdings,
  count(distinct address) as address_count
from 
  ethereum.assets.balances_latest 
where 
  token_type = 'ETH'
  and token_address = '0x0000000000000000000000000000000000000000'
  and address IN ( -- selecting address of Opensea buyers in the last 7 days 
    select 
      distinct buyer_address 
    from 
      ethereum.nfts.trades 
    where 
      usd_price > 0 
      and current_date - date(block_timestamp) < 7
      and marketplace = 'opensea'
  )
group by eth_holdings
order by eth_holdings asc

EVM Assets Balances Latest