select date_trunc(day, recorded_at) as time,

symbol,

avg (price) as price_usd,

address

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

group by 1,2,3

),

token_balance as (

select date_trunc(day,date) as time1,

symbol,

currency,

avg (balance/pow(10,decimal)) as avg_balance,

avg (balance*price_usd/pow(10,decimal)) as avg_balance_usd,

rank() over(partition by time order by avg_balance_usd desc) as ranking

from osmosis.core.fact_daily_balances o join token_price t on o.currency = t.address and date = time

group by 1,2,3

having avg_balance_usd > 0)

select *

from token_balance

where ranking <= 10

order by time1