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

,avg(lp_provided)over (order by tab2.date asc)as total_daily_avg_lp_provided

,avg(lp_removed)over (order by tab2.date asc)as total_daily_avg_lp_removed

from tab1

full outer join tab2

on tab1.date=tab2.date