select distinct date_trunc('hour',block_timestamp) as hour, 'dex swap' as type, tx_hash

from ethereum.core.ez_dex_swaps

where (symbol_in='WETH' or symbol_out='WETH')

and block_timestamp::date = '2022-05-25'

),

erc_20_tx as (

SELECT distinct date_trunc('hour',block_timestamp) as hour, 'erc20 transfer' as type, tx_hash

from ethereum.core.fact_token_transfers

where contract_address ='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

and block_timestamp::date = '2022-05-25'

and tx_hash not in (select tx_hash from dex_swap_tx)

select a.hour, type,

avg(b.gas_used*block_header_json:baseFeePerGas/1e18*price) as gas_base_fee,

avg(b.gas_used*(b.gas_price*1e9-block_header_json:baseFeePerGas)/1e18*price) as gas_priority_fee,

avg(tx_fee*price) as gas_fee_total

from dex_swap_tx a

left join ethereum.core.fact_transactions b

on a.tx_hash=b.tx_hash

left join ethereum.core.fact_blocks c

on b.BLOCK_NUMBER=c.BLOCK_NUMBER

left join crosschain.core.ez_hourly_prices d

on a.hour=d.hour

and d.symbol = 'WETH'

and d.blockchain = 'ethereum'

group by 1,2

union all

select a.hour, type,

avg(b.gas_used*block_header_json:baseFeePerGas/1e18*price) as gas_base_fee,

avg(b.gas_used*(b.gas_price*1e9-block_header_json:baseFeePerGas)/1e18*price) as gas_priority_fee,

avg(tx_fee*price) as gas_fee_total

from erc_20_tx a

left join ethereum.core.fact_transactions b