select

block_timestamp,

tx_hash,

origin_function_signature,

origin_from_address,

origin_to_address,

event_inputs:proposalId::integer as pID,

event_inputs:reason::string as reason,

event_inputs:support as supported,

event_inputs:voter::string as voter_address,

event_inputs:votes::integer as vote_count

from ethereum.core.fact_event_logs

WHERE contract_address = '0x5d2c31ce16924c2a71d317e5bbfd5ce387854039'

AND event_name = 'VoteCast'

pID,

CASE

WHEN supported = '1' THEN 'Approved'

WHEN supported = '0' THEN 'Rejected'

WHEN supported = '2' THEN 'Abstain' end as vote,

COUNT(supported) as number_of_votes,

COUNT(DISTINCT voter_address) as unique_voters,

SUM(vote_count) as total_voting_weight,

total_voting_weight / unique_voters as average_voting_weight,

min(block_timestamp)

FROM tab1

GROUP BY 1,2