-- forked from f851fda5-940d-4f16-a3c7-de95a5e85713
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 - 180
AND symbol = 'WETH'
GROUP BY pdate
)
SELECT
proposal_id,
CASE WHEN support = TRUE THEN 'For'
WHEN support = False THEN 'Against'
END AS "Support",
COUNT(DISTINCT a.tx_hash) AS "Votes",
COUNT(DISTINCT a.voter) AS "Voters"
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, "Support"