select date_trunc( 'day',block_timestamp) as date,

case when date < '2022-11-15' then 'Before being POTW'

when date >= '2022-11-15' then 'After being POTW'

end as period, action,

count (distinct tx_id) as count_of_transactions,

count (distinct liquidity_provider_address) as count_of_LPers,

sum (amount*price_in_USD/pow(10,decimal)) as Total_USD_amount,

count_of_transactions/count_of_LPers as avg_txn_per_LPer,

Total_USD_amount/count_of_transactions as avg_USD_amount_per_txn

from osmosis.core.fact_liquidity_provider_actions t1

join (select date_trunc( 'day',recorded_at) as day, address, symbol, avg (price) as price_in_USD

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

where symbol != 'IOV'

group by 1,2,3

union all

select date_trunc( 'day',recorded_at) as day,

'ibc/92BE0717F4678905E53F4E45B2DED18BC0CB97BF1F8B6A25AFEDF3D5A879B4D5' as address1,

'IST' as symbol1,

'1' as price_in_USD

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

group by 1,2,3) as price_tab

on t1.currency = price_tab.address and t1.block_timestamp::Date = price_tab.day

where tx_status = 'SUCCEEDED'

and pool_id[0] = 837

and block_timestamp <= current_date - 1

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

group by 1,2,3