select recorded_at::date as day,

symbol,

address,

avg (price) as USDPrice

from osmosis.core.dim_prices table_whale_type join osmosis.core.dim_labels table_whale_type2 on project_name = symbol

group by 1,2,3),

wallettypes as (

select 'Humpback Whale(balance bigger than 10,000,000 USD)' as type_of_wallet,

table_whale_type.address,

sum (balance*usdprice/pow(10,decimal)) as USD_Balance

from osmosis.core.fact_daily_balances table_whale_type join value table_whale_type2 on table_whale_type.currency = table_whale_type2.address and table_whale_type.date = table_whale_type2.day

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

group by 1,2 having usd_balance >= 10000000

union ALL

select 'Whale(balance smaller than 10,000,000 USD)' as type_of_wallet,

table_whale_type.address,

sum (balance*usdprice/pow(10,decimal)) as USD_Balance

from osmosis.core.fact_daily_balances table_whale_type join value table_whale_type2 on table_whale_type.currency = table_whale_type2.address and table_whale_type.date = table_whale_type2.day

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

group by 1,2 having usd_balance >= 1000000 and usd_balance < 10000000

union ALL

select 'Shark(balance smaller than 1,000,000 USD)' as type_of_wallet,

table_whale_type.address,

sum (balance*usdprice/pow(10,decimal)) as USD_Balance

from osmosis.core.fact_daily_balances table_whale_type join value table_whale_type2 on table_whale_type.currency = table_whale_type2.address and table_whale_type.date = table_whale_type2.day

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

group by 1,2 having usd_balance >= 100000 and usd_balance < 1000000

union ALL