avg (price) as ETHPrice

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1

),

avaxpricet as (SELECT BLOCK_TIMESTAMP::date as day,

avg(AMOUNT_IN_USD/AMOUNT_IN) as avaxprice

FROM avalanche.sushi.ez_swaps

WHERE symbol_in = 'WAVAX'

and symbol_out = 'USDC'

group by 1

)

SELECT date_trunc('day', block_timestamp) as day,

'Optimism' as block_chain,

COUNT(DISTINCT tx_hash) as trxs,

count(DISTINCT from_address) as users,

sum (tx_fee*ethprice) as Total_Fees,

(sum (tx_fee*ethprice))/(COUNT(DISTINCT tx_hash)) as avg_fee_per_trx,

count (case when STATUS != 'SUCCESS' then 1 end) as Failed_TX,

count (case when STATUS = 'SUCCESS' then 1 end) as Success_TX,

(Success_TX / (Success_TX + Failed_TX)) * 100 as Success_Rate,

count (distinct tx_hash)/2332800 as TPS

FROM optimism.core.fact_transactions t1 join ethpricet t2 on t1.block_timestamp::date = t2.day

where block_timestamp > '2022-01-01'

GROUP BY 1,2

UNION all

SELECT date_trunc('day', block_timestamp) as day,

'Arbitrum' as block_chain,

COUNT(DISTINCT tx_hash) as trxs,

count(DISTINCT from_address) as users,

sum (tx_fee*ethprice) as Total_Fees,

(sum (tx_fee*ethprice))/(COUNT(DISTINCT tx_hash)) as avg_fee_per_trx,