select avg(price) as price

from osmosis.core.dim_prices

where symbol = 'SCRT'

and date(RECORDED_AT) = CURRENT_DATE - 1

),

wallets_balances as (

select address,

sum(balance/1e6) as tbalance,

sum(balance/1e6) * price as usd_balance

from osmosis.core.fact_daily_balances

join ev_price_tbl

where date = CURRENT_DATE - 1

and currency = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'

group by address, price

),

balances_dist as (

case

when tbalance >=10000 then '>10k'

when tbalance <10000 and tbalance >= 5000 then '5k - 10k'

when tbalance <5000 and tbalance >= 1000 then '1k - 5k'

when tbalance <1000 and tbalance >= 100 then '100 - 1k'

when tbalance <100 and tbalance >= 10 then '10 - 100'

when tbalance <10 and tbalance>=1 then '1-10'

when tbalance <1 then '<1'

end as bal_dist

from wallets_balances

SELECT bal_dist, count(bal_dist) as counts from balances_dist

where bal_dist is not NULL

group by bal_dist