CyberaResearch2 JUP voter ranking allocation + snapshot Nov 1st copy
Updated 2024-12-19
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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