RECORDED_AT::date as day,

SYMBOL,

avg(PRICE) as price_token

from osmosis.core.dim_prices

where symbol not in ('IOV','JUNO')

group by 1,2),

pools as (select

POOL_ID,

concat(a.PROJECT_NAME,'/',b.PROJECT_NAME) as pool

from osmosis.core.fact_pool_hour s

left join osmosis.core.dim_tokens a on s.TOKEN_0_CURRENCY=a.address

left join osmosis.core.dim_tokens b on s.TOKEN_1_CURRENCY=b.address

select

distinct pool,

count(distinct tx_id) as no_actions,

count(distinct LIQUIDITY_PROVIDER_ADDRESS) as no_users,

sum((AMOUNT/pow(10,b.DECIMAL))*price_token) as volume_usd,

row_number() over (order by volume_usd desc) as rank

from osmosis.core.fact_liquidity_provider_actions s

left join osmosis.core.dim_tokens b on s.CURRENCY=b.address

left join tb8 a on b.PROJECT_NAME=a.SYMBOL and s.block_timestamp::date=a.day

join pools x on s.pool_id[0]=x.POOL_ID

where TX_SUCCEEDED='TRUE'

and ACTION='pool_joined'

and block_timestamp>=CURRENT_DATE - {{Time_period_days}}

group by 1 having volume_usd is not NULL

order by 4 desc limit 10