with tab1 as (

select BLOCK_TIMESTAMP::date as date,sum(AMOUNT)/1e6as lp_provided,count(distinct LIQUIDITY_PROVIDER_ADDRESS)as lpers

,count(distinct tx_id)as lp_provide_count

from osmosis.core.fact_liquidity_provider_actions

where CURRENCY='ibc/1480B8FD20AD5FCAE81EA87584D269547DD4D436843C1D20F15E00EB64743EF4'

and ACTION='pool_joined'

and TX_STATUS='SUCCEEDED'

and date>=current_date-7

group by 1),

tab2 as (

select BLOCK_TIMESTAMP::date as date,-sum(AMOUNT)/1e6as lp_removed,count(distinct LIQUIDITY_PROVIDER_ADDRESS)as lp_removers

,count(distinct tx_id)as lp_removed_count

from osmosis.core.fact_liquidity_provider_actions

where CURRENCY='ibc/1480B8FD20AD5FCAE81EA87584D269547DD4D436843C1D20F15E00EB64743EF4'

and ACTION='pool_exited'

and TX_STATUS='SUCCEEDED'

and date>=current_date-7

group by 1)

select tab2.date,lp_provided,lpers,lp_provide_count,lp_removed,lp_removers,lp_removed_count

from tab1

full outer join tab2

on tab1.date=tab2.date ),

act2 as (

select RECORDED_AT::date as date , avg(PRICE)as akt_price

from osmosis.core.dim_prices

where date>=current_date-7

and SYMBOL='AKT'

group by 1)

select act2.date,lp_provided*akt_price as lp_provide_usd,lp_removed*akt_price as lp_removed_usd

, sum(lp_provide_usd+lp_removed_usd)over(order by act2.date)as LP_volume_growth

from act1

full outer join act2

on act1.date=act2.date