iamjackV3 Solana Usage
Updated 2025-03-24
99
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
›
⌄
-- Define the configurable date range for the query.
WITH dates AS (
SELECT
'2025-03-17'::DATE AS start_date,
'2025-03-23'::DATE AS end_date
),
-- Step 1: Identify feeds updated within the date range.
updated_feeds AS (
SELECT
decoded_instruction:accounts[0]:pubkey::TEXT AS feed
FROM solana.core.fact_decoded_instructions AS i
CROSS JOIN dates
WHERE i.block_timestamp BETWEEN dates.start_date AND dates.end_date
AND program_id = 'SBondMDrcV3K4kxZR1HNVT7osZxAHVHgYXL5Ze1oMUv'
AND decoded_instruction:name IN (
'pull_feed_submit_response',
'pull_feed_submit_response_consensus',
'pull_feed_submit_response_consensus_light',
'pull_feed_submit_response_many',
'pull_feed_submit_response_svm'
)
GROUP BY feed
),
-- Step 2: Retrieve transactions in the date range that include only valid feed values,
-- and exclude unwanted program IDs.
transactions AS (
SELECT
acc.value AS feed,
ixn.value:programId::TEXT AS program_id
FROM solana.core.fact_transactions AS t
CROSS JOIN dates
CROSS JOIN LATERAL FLATTEN(input => t.instructions) AS ixn
CROSS JOIN LATERAL FLATTEN(input => ixn.value:accounts) AS acc
WHERE t.block_timestamp BETWEEN dates.start_date AND dates.end_date
QueryRunArchived: QueryRun has been archived