with

t1 as (

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

count(distinct tx_hash) as txs,

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

count(distinct origin_from_address) as users,

sum(event_inputs:value/1e6) as volume,

avg(event_inputs:value/1e6) 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 avalanche.core.fact_event_logs

where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and tx_hash in (select distinct tx_hash from avalanche.core.fact_event_logs where event_name = 'Swap')

and event_inputs:value/1e6 < 1e9 and tx_status = 'SUCCESS' and block_timestamp >= '2022-07-01'

group by 1

),

t2 as (

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

count(distinct tx_hash) as txs,

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

count(distinct origin_from_address) as users,

sum(event_inputs:value/1e6) as volume,

avg(event_inputs:value/1e6) 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 avalanche.core.fact_event_logs

where event_name = 'Transfer' and contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and tx_hash not in (select distinct tx_hash from avalanche.core.fact_event_logs where event_name = 'Swap')

and event_inputs:value/1e6 < 1e9 and tx_status = 'SUCCESS' and block_timestamp >= '2022-07-01'

group by 1

)

select 'Swaps' as type, * from t1

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