CyberaResearch2 JUP voter ranking allocation + snapshot Nov 1st copy
    Updated 2024-12-19
    -- forked from JUP voter ranking allocation + snapshot Nov 1st @ https://flipsidecrypto.xyz/studio/queries/1e174264-8ab8-4688-a29f-55fdf77e8837

    WITH filtered_proposals AS (
    SELECT
    ins.decoded_instruction:accounts[1]:pubkey AS proposal_id
    FROM
    solana.core.fact_decoded_instructions ins
    WHERE
    ins.block_timestamp::date BETWEEN '2024-03-01' AND '2024-10-30'
    AND ins.program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'
    AND ins.decoded_instruction:accounts[1]:name = 'proposal'
    GROUP BY
    ins.decoded_instruction:accounts[1]:pubkey
    HAVING
    COUNT(DISTINCT ins.tx_id) > 10
    ),

    jupiter_votes AS (
    SELECT
    ins.block_timestamp,
    ins.tx_id,
    ins.signers[0] AS user,
    ins.decoded_instruction:args:weight / POW(10, 6) AS jup_weight,
    ins.decoded_instruction:accounts[1]:pubkey AS proposal
    FROM
    solana.core.fact_decoded_instructions ins
    WHERE
    ins.block_timestamp::date BETWEEN '2024-03-01' AND '2024-10-30'
    AND ins.program_id = 'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'
    AND ins.event_type = 'setVote'
    AND ins.decoded_instruction:accounts[1]:name = 'proposal'
    AND ins.decoded_instruction:accounts[1]:pubkey IN (
    SELECT proposal_id FROM filtered_proposals
    )
    ),

    QueryRunArchived: QueryRun has been archived