HOUR,

symbol as token,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where symbol in ('WETH' , 'WMATIC' , 'WAVAX' , 'WBNB' )

group by 1,2

), main as (

select

DISTINCT a.tx_hash,

a.block_timestamp,

a.origin_from_address as sender,

avg(c.tx_fee * price) as fee

from arbitrum.core.fact_token_transfers a join arbitrum.core.fact_event_logs b on a.tx_hash=b.tx_hash

join arbitrum.core.fact_transactions c on c.tx_hash = a.tx_hash

join price d on a.block_timestamp::date = d.HOUR::date

where a.to_address LIKE lower('0xce16f69375520ab01377ce7b88f5ba8c48f8d666')

and a.contract_address LIKE lower('0xEB466342C4d449BC9f53A865D5Cb90586f405215')

and token = 'WETH'

and topics[0] = '0x999d431b58761213cf53af96262b67a069cbd963499fd8effd1e21556217b841'

group by 1,2,3

union

select

DISTINCT a.tx_hash,

a.block_timestamp,

a.origin_from_address as sender,

avg(c.tx_fee * price) as fee

from polygon.core.fact_token_transfers a join polygon.core.fact_event_logs b on a.tx_hash=b.tx_hash

join polygon.core.fact_transactions c on c.tx_hash = a.tx_hash

join price d on a.block_timestamp::date = d.HOUR::date

where a.to_address LIKE lower('0xce16f69375520ab01377ce7b88f5ba8c48f8d666')

and token = 'WMATIC'

and a.contract_address LIKE lower('0x750e4C4984a9e0f12978eA6742Bc1c5D248f40ed')

and topics[0] = '0x999d431b58761213cf53af96262b67a069cbd963499fd8effd1e21556217b841'

group by 1,2,3