date_trunc('day',hour) as date,

avg(price) as usdprice

from ethereum.core.fact_hourly_token_prices

where symbol='WETH'

group by 1

select 'Base' as type,

count(DISTINCT tx_hash) as txs,

count(DISTINCT from_address) as users,

sum(eth_value) as volume,

sum(eth_value*usdprice) as usd_volume,

sum(tx_fee) as fees,

avg(tx_fee) as avg_fees,

sum(gas_used) as gas,

avg(gas_used) as avg_gas

from base.goerli.fact_transactions a

join price b on a.block_timestamp::date=b.date

where status = 'SUCCESS'

group by 1