date_trunc('week', block_timestamp) as day,

COUNT(DISTINCT tx_hash) as txs,

COUNT(DISTINCT FROM_ADDRESS) as user,

sum(TX_FEE * price) as gas_eth_usd

FROM optimism.core.fact_transactions

LEFT outer JOIN (

SELECT

date_trunc('day', HOUR) as day1,

avg(PRICE) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE SYMBOL LIKE 'WETH'

GROUP by 1

) on date_trunc('day', block_timestamp) = day1

GROUP BY 1