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

,sum(lpers+lp_removers)over(order by tab2.date asc) as lpers_growth

,sum(lp_provide_count+lp_removed_count)over(order by tab2.date asc) as lp_count_growth

from tab1

full outer join tab2

on tab1.date=tab2.date

group by 1,2,3,4,5,6,7