address,

avg(price) as asset_price

from osmosis.core.dim_prices a

join osmosis.core.dim_labels b on a.symbol = b.project_name

group by 1,2),

table_1 as (select a.address as wallet_address,

sum(balance/pow(10,decimal) * asset_price) as total_balance

from osmosis.core.fact_daily_balances a

join price b on a.date = b.day and a.currency = b.address

where a.date = (select max(date) from osmosis.core.fact_daily_balances)

group by 1),

table_2 as (select wallet_address,

when total_balance < 10 then '1. Shrimp'

when total_balance >= 10 and total_balance < 1000 then '2. Fish'

when total_balance >= 1000 and total_balance < 10000 then '3. Dolphin'

else '4. Whale'

end as category,

total_balance

from table_1)

select category,

count(distinct(wallet_address)) as wallet_count,

row_number () over (order by wallet_count desc) as count

from table_2

group by 1

order by 2 desc