WITH vote_data AS(

SELECT

BLOCK_TIMESTAMP as datetime,

ORIGIN_FROM_ADDRESS as voter,

EVENT_INPUTS:proposalId::int as proposal, -- choose another data type is preferred

EVENT_INPUTS:votes::string as voting_power,

-- SUM(votes) over (ORDER BY datetime, proposal ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS total,

CASE

WHEN EVENT_INPUTS:support::string=0

THEN 'AGAINST'

WHEN EVENT_INPUTS:support::string=1

THEN 'FOR'

WHEN EVENT_INPUTS:support::string=2

THEN 'ABSTAIN'

END AS support,

COALESCE(EVENT_INPUTS:reason::string, '') as reason,

EVENT_NAME,

TX_HASH,

parse_json(EVENT_INPUTS::string) as EVENT_INPUTS

from ethereum.core.fact_event_logs

where contract_address='0x5d2c31ce16924c2a71d317e5bbfd5ce387854039'

and EVENT_INPUTS IS NOT NULL

and EVENT_NAME='VoteCast'

and tx_status='SUCCESS'

ORDER BY

-- proposal DESC,

datetime DESC

SELECT

proposal,

COALESCE(SUM(voting_power), '0') AS total,

COALESCE(SUM(CASE WHEN support='FOR' THEN voting_power END), 0) AS for_or_yes,

COALESCE(SUM(CASE WHEN support='AGAINST' THEN voting_power END), 0) AS against_or_no,

COALESCE(SUM(CASE WHEN support='ABSTAIN' THEN voting_power END), 0) AS abstain