t1 as (

recorded_hour::date as date,

symbol,

currency,

avg(price) as price_usd

from osmosis.core.ez_prices

group by 1, 2, 3

liquidity_provider_address,

count(distinct tx_id) as transactions,

count(distinct liquidity_provider_address) as users,

sum((amount * price_usd) / pow(10, decimal)) as volume_usd,

avg((amount * price_usd) / pow(10, decimal)) as average_volume_usd,

median((amount * price_usd) / pow(10, decimal)) as median_volume_usd

from osmosis.core.fact_liquidity_provider_actions a

join t1

on block_timestamp::date = date and t1.currency = a.currency

where pool_id[0] = '1011'

and action in ('pool_joined','pool_exited')

and tx_succeeded = 1

group by 1

order by volume_usd desc

limit 10