p as (select hour::date as date,

avg (price) as price

from ethereum.core.fact_hourly_token_prices

where symbol ilike 'wETH'

group by 1)

select

date_trunc('day', block_timestamp)::date as date ,

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,

count(distinct VOTER) as delegators,

avg (tx_fee*price) as avg_fee_usd,

total_fee_usd/tx_count as fee_per_tx,

total_fee_usd/block_count as fee_per_block,

total_fee_usd/delegators as fee_per_user

from Ethereum.core.fact_transactions

join p on block_timestamp::Date = date

join ethereum.aave.ez_votes using (tx_hash)

where block_timestamp >= CURRENT_DATE - {{days_back}}

group by 1,eth_price