select *

from osmosis.core.fact_liquidity_provider_actions left join osmosis.core.dim_labels on currency = address

where

block_timestamp::date >= '2022-09-26' and

currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7' and

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

tx_status = 'SUCCEEDED' and

project_name = 'DOT.axl' and

pool_id = 773

select

date_trunc('day', block_timestamp) as date,

action,

-- daily

count(distinct tx_id) as tx_count,

count(distinct liquidity_provider_address) as unique_user,

sum(amount/1e10) as tx_volume,

-- ma7

avg(tx_count) over (order by date, date rows between 6 preceding and current row) as ma7_tx_count,

avg(unique_user) over (order by date, date rows between 6 preceding and current row) as ma7_unique_user,

avg(tx_volume) over (order by date, date rows between 6 preceding and current row) as ma7_tx_volume

from base

group by 1, 2