select block_timestamp, gas_price, gas_used, gas_price * gas_used as amount_of_gas

from optimism.core.fact_transactions

where block_timestamp::date >= CURRENT_DATE - 30

and status = 'SUCCESS'

), optimism_average_gas as

select date_trunc('second', block_timestamp) as date, avg(gas_used) as average_gas_used, avg(amount_of_gas) / 1e18 as amount_of_average_gas

from optimism_gas

group by date

), ethereum_gas as

select block_timestamp, gas_price, gas_used, gas_price * gas_used as amount_of_gas

from ethereum.core.fact_transactions

where block_timestamp::date >= CURRENT_DATE - 30

and status = 'SUCCESS'

), ethereum_average_gas as

select date_trunc('second', block_timestamp) as date, avg(gas_used) as average_gas_used, avg(amount_of_gas) / 1e18 as amount_of_average_gas

from ethereum_gas

group by date

select optimism_average_gas.date as hour_date, optimism_average_gas.average_gas_used as optimism_gas_used,

optimism_average_gas.amount_of_average_gas as optimism_gas_amount , ethereum_average_gas.average_gas_used as ethereum_gas_used,

ethereum_average_gas.amount_of_average_gas as ethereum_gas_amount

from optimism_average_gas

join ethereum_average_gas on optimism_average_gas.date = ethereum_average_gas.date

order by hour_date