from avalanche.core.fact_event_logs

where event_name = 'Swap'),

temp as ( select 'Swap' as action_,origin_from_address, count(distinct tx_hash) as txns, sum(event_inputs:value/1e6) as Total_Vol

from avalanche.core.fact_event_logs

where tx_status = 'SUCCESS'

and contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and tx_hash in (select tx_hash from swaps)

and block_timestamp >= '2022-07-01'

and event_inputs:value/1e6 < 1e9

group by 1,2

union ALL

select 'Transfer' as action_, origin_from_address, count(distinct tx_hash) as txns, sum(event_inputs:value/1e6) as Total_Vol

from avalanche.core.fact_event_logs

where tx_status = 'SUCCESS'

and event_name = 'Transfer'

and contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and tx_hash not in (select tx_hash from swaps)

and block_timestamp >= '2022-07-01'

and event_inputs:value/1e6 < 1e9

group by 1,2)

select action_, avg(txns) as avg_txns, avg(total_vol) as Average_Vol

from temp

group by 1