with arb_price AS ( SELECT recorded_hour::date AS date , currency , avg(price) AS price FROM osmosis.core.ez_prices WHERE symbol IN ('ARB') GROUP BY 1, 2 ), transfer AS ( SELECT trn.block_timestamp::date AS date , transfer_type , sender , receiver , amount / power(10, decimal) AS amount_trn , amount / power(10, decimal) * price AS amount_trn_usd , foreign_chain , CASE when foreign_chain IS NULL then 'Internal Chain' else 'Foregin Chain' end AS chain_type FROM osmosis.core.fact_transfers trn JOIN arb_price prc on trn.block_timestamp::date = prc.date AND trn.currency = prc.currency WHERE tx_succeeded = true ), rcv AS ( SELECT receiver , sum(amount_trn_usd) AS amount_rcv_usd FROM transfer GROUP BY 1 ), snd AS ( SELECT sender , sum(amount_trn_usd) AS amount_snd_usd FROM transfer GROUP BY 1 ), summary AS ( SELECT receiver AS arb_holder , sum(coalesce(amount_rcv_usd, 0) - coalesce(amount_snd_usd, 0)) AS arb_hold_usd from rcv LEFT JOIN snd on rcv.receiver = snd.sender GROUP BY 1 HAVING arb_hold_usd > 0 ) SELECT CASE when arb_hold_usd < 10 then 'Less than $10 ARB' when arb_hold_usd >= 10 AND arb_hold_usd < 100 then '$10 - $100 ARB' when arb_hold_usd >= 100 AND arb_hold_usd < 500 then '$100 - $500 ARB' when arb_hold_usd >= 500 AND arb_hold_usd < 1000 then '$500 - $1K ARB' when arb_hold_usd >= 1000 AND arb_hold_usd < 10000 then '$1K - $10K ARB' when arb_hold_usd >= 10000 then 'More than $10K ARB' end AS holder_type , count(CASE when arb_hold_usd < 10 then 'Less than $10 ARB' when arb_hold_usd >= 10 AND arb_hold_usd < 100 then '$10 - $100 ARB' when arb_hold_usd >= 100 AND arb_hold_usd < 500 then '$100 - $500 ARB' when arb_hold_usd >= 500 AND arb_hold_usd < 1000 then '$500 - $1K ARB' when arb_hold_usd >= 1000 AND arb_hold_usd < 10000 then '$1K - $10K ARB' when arb_hold_usd >= 10000 then 'More than $10K ARB' end) AS count_holder_type , sum(arb_hold_usd) AS arb_hold_usd FROM summary GROUP BY 1

Exploring the ARB Token on Osmosis DEX