SELECT date(hour) as pdate, avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where date(hour) >= CURRENT_DATE - 180

AND symbol = 'WETH'

GROUP BY pdate

proposal_id,

COUNT(DISTINCT a.tx_hash) AS "Votes",

COUNT(DISTINCT a.voter) AS "Voters",

sum(tx_fee) as "Total Fee",

sum("Total Fee") over (order by proposal_id asc) as "Cumulative Fee",

sum(tx_fee * avg_price) as "Total Fee(USD)",

sum("Total Fee(USD)") over (order by proposal_id asc) as "Cumulative 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 - 180

GROUP BY proposal_id