from ethereum.core.fact_hourly_token_prices

where date >= CURRENT_DATE - 180

and symbol = 'WETH'

group by 1)

,

gas as ( select date(a.block_timestamp) as date, a.tx_hash, voter, proposal_id, voting_power, tx_fee, tx_fee*avg_price as fee_usd, avg_price

from ethereum.core.fact_transactions a join ethereum.aave.ez_votes b on a.tx_hash = b.tx_hash

left outer join price c on a.block_timestamp::date = c.date

where a.block_timestamp >= CURRENT_DATE - 180)

select date, count(DISTINCT(tx_hash)) as total_vote, count(DISTINCT(voter)) as total_voter,

avg(voting_power) as vote_power, count(DISTINCT(proposal_id)) as total_proposal,

sum(tx_fee) as total_eth, avg(tx_fee) as avg_fee_eth, sum(fee_usd) as total_fee_usd, avg(fee_usd) as avg_fee_usd,

median(fee_usd) as median_fee, max(fee_usd) as max_usd, min(fee_usd) as min_usd,

sum(total_eth) over (order by date asc) as cum_eth, sum(total_fee_usd) over (order by date asc) as cum_usd

from gas

group by 1