hour::date as date,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where token_address is null

and hour::date >= current_date() - 30

group by date),

gas as (

select

t.*,

price as eth_price,

((t.gas_price/1e9) * (t.gas_used)) * (price) as gas_used_usd

from ethereum.core.fact_transactions t

join eth_price p on date_trunc('hour', t.block_timestamp) = p.date

where t.block_timestamp >= current_date() - 30)

select sum(gas_used_usd) as total_gas_spent

from gas