with eth_transfer_raw as ( select FROM_ADDRESS as address, (-1) * VALUE as amount from ethereum.core.fact_transactions where VALUE > 0 AND STATUS = 'SUCCESS' union all select TO_ADDRESS as address, VALUE as amount from ethereum.core.fact_transactions where VALUE > 0 AND STATUS = 'SUCCESS' union all select FROM_ADDRESS as address, (-1) * TX_FEE as amount from ethereum.core.fact_transactions ), eth_price as ( select AVG(PRICE) as price from ethereum.price.ez_hourly_token_prices where SYMBOL = 'WETH' AND HOUR = CURRENT_DATE ), address_balance as ( select a.address, sum(a.amount * b.price) as balance_amount_usd from eth_transfer_raw a left join eth_price b on 1 = 1 where a.address is not null -- exclude the null address data group by 1 having sum(a.amount * b.price) >= 1 -- address has at least $1 ), total_holders as ( select count(address) as total_holders from address_balance ), ------------------------------------------------------------------- -- Blast ------------------------------------------------------------------- blast_transfer_raw as ( select FROM_ADDRESS as address, (-1) * VALUE as amount from blast.core.fact_transactions where VALUE > 0 AND STATUS = 'SUCCESS' union all select TO_ADDRESS as address, VALUE as amount from blast.core.fact_transactions where VALUE > 0 AND STATUS = 'SUCCESS' union all select FROM_ADDRESS as address, (-1) * TX_FEE as amount from blast.core.fact_transactions ), blast_address_balance as ( select a.address, sum(a.amount * b.price) as blast_balance_amount_usd from blast_transfer_raw a left join eth_price b on 1 = 1 -- left join labels.addresses d on a.address = d.address and d.blockchain = 'mode' where a.address is not null -- exclude the null address data group by 1 having sum(a.amount * b.price) >= 1 -- address has at least $1 ), result as ( select ( case when balance_amount_usd >= 3000000 then 'More than 3M' when balance_amount_usd >= 1000000 and balance_amount_usd < 3000000 then '$1M-3M' when balance_amount_usd >= 500000 and balance_amount_usd < 1000000 then '$500K-1M' when balance_amount_usd >= 300000 and balance_amount_usd < 500000 then '$300K-500K' when balance_amount_usd >= 200000 and balance_amount_usd < 300000 then '$200K-300K' when balance_amount_usd >= 100000 and balance_amount_usd < 200000 then '$100K-200k' when balance_amount_usd >= 10000 and balance_amount_usd < 100000 then '$10K-100K' when balance_amount_usd >= 5000 and balance_amount_usd < 10000 then '$5K-10K' when balance_amount_usd >= 1000 and balance_amount_usd < 5000 then '$1K-5K' when balance_amount_usd >= 500 and balance_amount_usd < 1000 then '$500-1K' when balance_amount_usd >= 100 and balance_amount_usd < 500 then '$100-500' when balance_amount_usd >= 50 and balance_amount_usd < 100 then '$50-100' when balance_amount_usd >= 10 and balance_amount_usd < 50 then '$10-50' else 'Less than $10' end ) as amount_usd_range, count(address) as holder_count, avg(balance_amount_usd) as average_balance_amount, sum(balance_amount_usd) as total_balance_amount, avg(blast_balance_amount_usd) as blast_average_balance_amount from ( SELECT a.address AS address, COALESCE(a.balance_amount_usd, 0) AS balance_amount_usd, COALESCE(b.blast_balance_amount_usd, 0) AS blast_balance_amount_usd FROM address_balance a FULL OUTER JOIN blast_address_balance b ON a.address = b.address ) group by 1 ) select a.amount_usd_range, a.holder_count, cast(a.holder_count as double) / cast(b.total_holders as double) as pct_of_total_holders, a.average_balance_amount, a.total_balance_amount, a.blast_average_balance_amount from result a left join total_holders b on 1 = 1 order by 5 desc

Blast Basic Metrics