with eth_price as (
SELECT date(hour) as pdate, avg(price) as avg_price
from ethereum.core.fact_hourly_token_prices
where date(hour) >= CURRENT_DATE - 90
AND symbol = 'WETH'
GROUP BY pdate
)
SELECT
proposal_id,
COUNT(DISTINCT a.tx_hash) AS "Votes",
COUNT(DISTINCT a.voter) AS "Voters",
sum(tx_fee) as "Total Fee",
sum(tx_fee * avg_price) as "Total Fee(USD)",
sum(gas_used / 1e9) as "Total Gas",
avg(gas_used / 1e9) as "Average Gas"
FROM ethereum.aave.ez_votes a
JOIN ethereum.core.fact_transactions b ON a.tx_hash = b.tx_hash
JOIN eth_price on date(a.block_timestamp) = pdate
where date(a.block_timestamp) >= CURRENT_DATE - 90
GROUP BY proposal_id