SELECT

signers [0] AS voter,

decoded_instruction:accounts [1] :pubkey AS proposal,

decoded_instruction:args:weight / pow(10, 6) AS vote_weight,

block_timestamp

FROM

solana.core.ez_events_decoded

WHERE

1 = 1

AND block_timestamp :: date >= '2024-08-22'

AND program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'

AND event_type = 'setVote'

AND proposal = 'CudfcXyY6JWKFkZxHMLv9ZvJuzBat4UvTx6anzW33TvM'

AND succeeded

),

latest_vote AS (

SELECT

voter,

MAX(block_timestamp) AS latest

FROM

vote

GROUP BY

voter

COUNT(DISTINCT v.voter) AS voters,

SUM(v.vote_weight) AS total_vote_weight,

MIN(v.vote_weight) AS min_vote_weight,

MAX(v.vote_weight) AS max_vote_weight,

AVG(v.vote_weight) AS avg_vote_weight

vote v

JOIN latest_vote lv ON v.voter = lv.voter

AND v.block_timestamp = lv.latest;