select date as last_date

from osmosis.core.fact_daily_balances

order by 1 desc

limit 1

), price as

select RECORDED_AT::date as date

, address

, avg(price) as avg_price

from osmosis.core.dim_prices prc

join osmosis.core.dim_labels lbl on prc.symbol = lbl.project_name

group by 1, 2

), daily_balance as

select blc.date as date

, blc.address as wallet_address

, (blc.balance / pow(10, decimal)) * prc.avg_price as balance_usd

, case

when balance_usd <= 10 then 'Shrimp (balance less than $10)'

when balance_usd > 10 and balance_usd <= 100 then 'Crab (balance between $10 and $100)'

when balance_usd > 100 and balance_usd <= 1000 then 'Octopus (balance between $100 and $1k)'

when balance_usd > 1000 and balance_usd <= 5000 then 'Fish (balance between $1k and $5k)'

when balance_usd > 5000 and balance_usd <= 10000 then 'Dolphin (balance between $5k and $10k)'

when balance_usd > 10000 and balance_usd <= 100000 then 'Shark (balance between $10k and $100k)'

when balance_usd > 100000 and balance_usd <= 1000000 then 'Whale (balance between $100k and $1m)'

when balance_usd > 1000000 then 'Humpback (balance greater than $1m)'

end as wallet_type

from osmosis.core.fact_daily_balances blc

join price prc on blc.currency = prc.address

and blc.date = prc.date

where blc.date = (select last_date from date_of_last_price)

), wallet_balance as

select wallet_type