select

date_trunc('day', recorded_at) as day,

address,

avg(price) as price

from

osmosis.core.dim_prices x

join osmosis.core.dim_labels y on x.symbol = y.project_name

group by

1,

2

),

balances as (

select

x.address as wallet_address,

sum(balance*price/pow(10,decimal)) as balance

from

osmosis.core.fact_daily_balances x

join price b on x.date = b.day

and x.currency = b.address

where

x.date = (

select

max(date)

from

osmosis.core.fact_daily_balances

)

group by

1

),

types as (

select

wallet_address,

case when Balance < 1 then '1.Shimp' when Balance >= 1

and Balance < 10 then '2.Crab' when Balance >= 10

and Balance < 100 then '3.Octopus' when Balance >= 100