TO_NUMBER(proposals.proposal_id) AS "Proposal",

status AS "Status",

ROUND(AVG(end_voting_period - start_voting_period)) AS "Voting Period",

CASE

WHEN "Voting Period" = 19200 THEN 'Short'

WHEN "Voting Period" = 64000 THEN 'Long'

END AS "Type",

COUNT(DISTINCT tx_hash) AS "Votes",

COUNT(DISTINCT voter) AS "Voters",

CASE

WHEN "Type" = 'Short' THEN 320000

WHEN "Type" = 'Long' THEN 3200000

END AS "Quorum",

CASE

WHEN "Type" = 'Short' THEN 80000

WHEN "Type" = 'Long' THEN 2400000

END AS "Differential",

ROUND(SUM(voting_power) / POW(10, 18)) AS "Voting Powers",

ROUND(SUM(IFF(support = TRUE, TO_NUMBER(voting_power), 0)) / POW(10, 18)) AS "For Voting Powers",

ROUND(SUM(IFF(support = FALSE, TO_NUMBER(voting_power), 0)) / POW(10, 18)) AS "Against Voting Powers",

("For Voting Powers" - "Against Voting Powers") AS "Vote Differential",

ABS("Vote Differential") AS "Absolute Vote Difference",

CASE

WHEN "Vote Differential" > 0 THEN 'In Favor'

WHEN "Vote Differential" < 0 THEN 'Against'

END AS "Dominance"

ethereum.aave.ez_proposals proposals

JOIN ethereum.aave.ez_votes votes ON proposals.proposal_id = votes.proposal_id

GROUP BY

"Proposal",

"Status"

ORDER BY

"Proposal" DESC