SELECT

date_trunc('hour', block_timestamp) AS hour,

signers [0] AS voter,

decoded_instruction:accounts [1] :pubkey AS proposal,

decoded_instruction:args:side AS vote_choice,

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-09-26'

AND program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'

AND event_type = 'setVote'

AND proposal = '5G3ut22wiUWAoSnHCNTHus8LJTcnUaJKzjH1j1vLvoKs'

AND succeeded

),

latest_vote AS (

SELECT

voter,

MAX(block_timestamp) AS latest

FROM

vote

GROUP BY

voter

CASE

WHEN v.vote_weight < 100 THEN 'plankton : <100'

WHEN v.vote_weight < 1000 THEN 'fish : <1000'

WHEN v.vote_weight < 10000 THEN 'shrimp : <10,000'

WHEN v.vote_weight < 100000 THEN 'dolphin : <100,000'

ELSE 'whale : >100,000'

END AS category,