prices AS (

SELECT

hour::date AS day,

AVG(price) AS price

FROM

ethereum.core.fact_hourly_token_prices

WHERE

symbol = 'WETH'

GROUP BY

day

),

overall AS (

SELECT

COUNT(DISTINCT voting.proposal_id) AS proposals,

COUNT(DISTINCT voting.voter) AS voters,

COUNT(DISTINCT voting.tx_hash) AS votes,

SUM(transactions.tx_fee) AS fees,

SUM(transactions.tx_fee) * AVG(prices.price) AS fees_usd,

SUM(transactions.gas_used) AS gas,

votes / proposals AS votes_proposal,

voters / proposals AS voters_proposal,

fees / proposals AS fees_proposal,

fees_usd / proposals AS fees_usd_proposal,

gas / proposals AS gas_proposal,

fees / votes AS fees_vote,

fees_usd / votes AS fees_usd_vote,

gas / votes AS gas_vote

FROM

ethereum.aave.ez_votes voting

JOIN ethereum.core.fact_transactions transactions ON voting.tx_hash = transactions.tx_hash

JOIN prices ON voting.block_timestamp::date = prices.day

),

six_months AS (

SELECT

COUNT(DISTINCT voting.proposal_id) AS proposals,