select

trunc(hour,'day') as edate,

avg(price) as eth_price

from optimism.core.fact_hourly_token_prices

where SYMBOL ilike '%eth'

and edate >= current_date - 90

group by 1),

arb as (

select

date_trunc('day',BLOCK_TIMESTAMP) as adate,

count(distinct TX_HASH) as arb_tx,

count(distinct FROM_ADDRESS) as arb_user,

sum(tx_fee*eth_price) as arb_fee_usd,

sum(ETH_VALUE*eth_price) as arb_tx_usd_value,

arb_fee_usd/arb_tx as arb_fee_per_tx,

arb_tx_usd_value/arb_tx as eth_value_per_tx

from arbitrum.core.fact_transactions ft join ethprice ep on ft.BLOCK_TIMESTAMP::date = ep.edate

where adate >= current_date - 90

and STATUS = 'SUCCESS'

group by 1

) ,

op as (

select

date_trunc('day',BLOCK_TIMESTAMP) as odate,

count(distinct TX_HASH) as op_tx,

count(distinct FROM_ADDRESS) as op_user,

sum(tx_fee*eth_price) as op_fee_usd,

sum(ETH_VALUE*eth_price) as op_tx_usd_value,

op_fee_usd/op_tx as op_fee_per_tx,

op_tx_usd_value/op_tx as eth_value_per_tx

from optimism.core.fact_transactions ot join ethprice ep on ot.BLOCK_TIMESTAMP::date = ep.edate

where odate >= current_date - 90

and STATUS = 'SUCCESS'

group by 1