select

HOUR::date as date,

avg(PRICE) as ETH_price

from ethereum.core.fact_hourly_token_prices

where 1=1

and symbol = 'WETH'

and hour >= CURRENT_DATE - 30

group by 1

order by date ASC

),

OP_Price as (

select

HOUR::date as date,

avg(PRICE) as OP_price

from optimism.core.fact_hourly_token_prices

where SYMBOL = 'OP'

and HOUR >= CURRENT_DATE - 30

group by 1

),

gas as (

select

BLOCK_TIMESTAMP::date as date,

tx_hash,

TX_FEE * ETH_price as Gas_USD

from optimism.core.fact_transactions a join ETH_price b on a.BLOCK_TIMESTAMP::date = b.date

where 1=1

and STATUS = 'SUCCESS'

and BLOCK_TIMESTAMP >= CURRENT_DATE - 30

and BLOCK_TIMESTAMP < CURRENT_DATE

and TX_FEE is not null

a.date,

count(distinct tx_hash) as "# TXs",

sum(Gas_USD) as "Gas Amount-USD",