p as (select hour,

price

from ethereum.core.fact_hourly_token_prices

where symbol ilike 'wETH'

)

select

date_trunc('hour', block_timestamp) as hour ,

count(distinct tx_hash) as tx_count,

count(distinct BLOCK_NUMBER) as block_count,

price as eth_price,

sum (tx_fee*price) as total_fee_usd,

total_fee_usd/tx_count as fee_per_tx,

total_fee_usd/block_count as fee_per_block

from Ethereum.core.fact_transactions

join p on date_trunc('hour', block_timestamp) = hour

join ethereum.aave.ez_votes using (tx_hash)

where block_timestamp >= CURRENT_DATE - {{days_back}}

group by 1,eth_price