with

t1 as (

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

case when date>='2023-05-25' then 'After expected upgrade' else 'Before expected upgrade' end as period,

count(distinct tx_hash) as txs,

sum(txs) over (order by date) as cum_txs,

count(distinct origin_from_address) as users,

sum(case when token_in = lower('0x65ef703f5594d2573eb71aaf55bc0cb548492df4') then amount_in else amount_out end) as volume,

avg(case when token_in = lower('0x65ef703f5594d2573eb71aaf55bc0cb548492df4') then amount_in else amount_out end) as avg_volume,

avg(avg_volume) over (order by date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as avg_volume_ma_7hour,

sum(volume) over (order by date) as cum_volume

from ethereum.core.ez_dex_swaps

where (token_in = lower('0x65ef703f5594d2573eb71aaf55bc0cb548492df4')

or token_out = lower('0x65ef703f5594d2573eb71aaf55bc0cb548492df4'))

and block_timestamp >= current_date-INTERVAL '1 MONTH'

group by 1,2

),

t2 as (

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

case when date>='2023-05-25' then 'After expected upgrade' else 'Before expected upgrade' end as period,

count(distinct tx_hash) as txs,

sum(txs) over (order by date) as cum_txs,

count(distinct origin_from_address) as users,

sum(amount) as volume,

avg(amount) as avg_volume,

avg(avg_volume) over (order by date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as avg_volume_ma_7hour,

sum (volume) over (order by date) as cum_volume

from ethereum.core.ez_token_transfers

where contract_address = lower('0x65ef703f5594d2573eb71aaf55bc0cb548492df4')

and block_timestamp >= current_date-INTERVAL '1 MONTH'

group by 1,2

)

select 'Swaps' as type, * from t1

union select 'Transfers' as type, * from t2 order by 2 asc ,1