count (distinct tx_id) as trxs,

count (distinct liquidity_provider_address) as users,

sum (amount*usdprice/pow(10,decimal)) as USD_Volume,

avg (amount*usdprice/pow(10,decimal)) as Avg_USD_Volume

from osmosis.core.fact_liquidity_provider_actions a join (select recorded_at::date as day,

address,

symbol,

avg (price) as USDPrice

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

where symbol != 'IOV'--credit to alik110(IOV token’s price has a sudden and certainly incorrect spike on October 2021)

group by 1,2,3) b on a.block_timestamp::date = b.day and a.currency = b.address

where tx_status = 'SUCCEEDED'

and tx_id in (select tx_id

from (select tx_id,

count (tx_id) as TXS_Count

from osmosis.core.fact_liquidity_provider_actions

where action = 'pool_joined'

and tx_status = 'SUCCEEDED'

group by 1

having TXS_Count < 2

)

)

group by 1

union ALL

select 'add both assets' as type,

count (distinct tx_id) as trxs,

count (distinct liquidity_provider_address) as users,

sum (amount*usdprice/pow(10,decimal)) as USD_Volume,

avg (amount*usdprice/pow(10,decimal)) as Avg_USD_Volume

from osmosis.core.fact_liquidity_provider_actions a join (

select recorded_at::date as day,

address,

symbol,