SELECT

DATE_TRUNC('day', block_timestamp) AS voting_day,

signers[0] AS voter_address,

decoded_instruction:accounts[1]:pubkey AS proposal_id,

decoded_instruction:args:side AS selected_option,

decoded_instruction:args:weight / POW(10, 6) AS vote_size,

block_timestamp

FROM solana.core.ez_events_decoded

WHERE

block_timestamp::date >= '2024-11-25'

AND program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'

AND event_type = 'setVote'

AND proposal_id = 'DZ31es2JWLmTBS69ex7FcMfUrCh2LFRo4jqDNcZuoGV7'

AND succeeded

),

most_recent_votes AS (

SELECT

voter_address,

MAX(block_timestamp) AS latest_timestamp

FROM votes_table

GROUP BY voter_address

CASE

WHEN vt.selected_option = '1' THEN 'Yes'

WHEN vt.selected_option = '2' THEN 'No'

WHEN vt.selected_option = '3' THEN 'Abstain'

ELSE 'Unknown'

END AS final_vote_choice,

SUM(vt.vote_size) AS total_votes,

COUNT(DISTINCT vt.voter_address) AS unique_voters,

ROUND(

SUM(vt.vote_size) * 100.0

/ NULLIF(SUM(SUM(vt.vote_size)) OVER(), 0),

2