select

date_trunc('day',date) as date_,

sum (balance/pow(10,decimal)) as total_volume,

currency as currency_

from osmosis.core.fact_daily_balances

where currency ilike '%pool%'

and currency in (select currency from osmosis.core.fact_liquidity_provider_actions where currency ilike '%pool%')

and balance_type = 'locked liquidity'

group by date_,currency_)

select

date(block_timestamp) as date,

total_volume,

count (distinct tx_id) as total_transaction,

count (distinct liquidity_provider_address) as total_provider

from osmosis.core.fact_liquidity_provider_actions x

join volume y on x.block_timestamp::date = y.date_

and x.currency = y.currency_

where currency ilike '%pool%'

and tx_status = 'SUCCEEDED'

and block_timestamp::date >= CURRENT_DATE - 180

group by date,total_volume

ORDER by date