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_currentwhich reflects the current USD value of the asset.
usd_balance_at_block_timestampwhich reflects the USD value of the asset at the last transaction activity.
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.
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
token_type = 'ETH'
and token_address = '0x0000000000000000000000000000000000000000'
and address IN ( -- selecting address of Opensea buyers in the last 7 days
usd_price > 0
and current_date - date(block_timestamp) < 7
and marketplace = 'opensea'
group by eth_holdings
order by eth_holdings asc