WITH proposal_info AS (

SELECT

ins.decoded_instruction:accounts[1]:pubkey AS proposal_id,

ins.block_timestamp AS vote_time,

ins.tx_id AS transaction_id,

ins.signers[0] AS voter,

ins.decoded_instruction:args:side AS vote_side,

ins.decoded_instruction:args:weight / POW(10, 6) AS vote_weight

FROM

solana.core.fact_decoded_instructions ins

WHERE

ins.block_timestamp::date >= '2024-03-01'

AND ins.program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'

AND ins.decoded_instruction:accounts[1]:name = 'proposal'

--AND side IS NOT NULL

--AND weight IS NOT NULL

AND event_type = 'setVote'

),

filtered_proposals AS (

SELECT

proposal_id,

COUNT(DISTINCT transaction_id) AS number_of_votes,

MIN(vote_time) AS first_vote_time,

MAX(vote_time) AS last_vote_time,

COUNT(DISTINCT voter) AS number_of_unique_voters,

ROUND(SUM(vote_weight)) AS total_vote_weight

FROM

proposal_info

GROUP BY

proposal_id

HAVING

COUNT(DISTINCT transaction_id) >= 10