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

voter,

count(distinct tx_hash) as tx_count,

round(sum (tx_fee*price),2) as total_fee_usd,

round ( (total_fee_usd/tx_count),2) as fee_per_tx,

round ((total_fee_usd/{{days_back}}),2) as fee_per_day,

round ((tx_count/{{days_back}}),2) as txn_per_day,

count(distinct BLOCK_NUMBER) as block_count,

round ((total_fee_usd/block_count),2) as fee_per_block

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 order by 3 desc limit 10