CyberaResearchJUP Staking - v2 Current Unstaking Amounts copy
Updated 2024-11-25
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 Staking - v2 Current Unstaking Amounts @ https://flipsidecrypto.xyz/studio/queries/ce51e653-9fc7-4415-92b0-e1102e6545e5
-- 1. Generate a list of snapshot dates from 2024-03-01 to the current date
WITH snapshot_dates AS (
SELECT
DATEADD(day, seq4(), '2024-03-01') AS snapshot_date
FROM
TABLE(GENERATOR(ROWCOUNT => 1000)) -- Generates up to 1000 days; adjust if needed
WHERE
DATEADD(day, seq4(), '2024-03-01') <= CURRENT_DATE()
),
-- 2. Retrieve and aggregate increaseLockedAmount events per day from 2024-03-01 to the current date
staking_increase_events AS (
SELECT
DATE(block_timestamp) AS event_date,
SUM(decoded_instruction:args:amount::int / POW(10, 6)) AS total_staking_amount
FROM
solana.core.ez_events_decoded
WHERE
program_id = 'voTpe3tHQ7AjQHMapgSue2HJFAh2cGsdokqN3XqmVSj' -- JUP staking program ID
AND event_type = 'increaseLockedAmount'
AND block_timestamp::date >= '2024-01-01'
AND block_timestamp::date <= CURRENT_DATE()
GROUP BY
DATE(block_timestamp)
),
-- 3. CTE for Wallet Withdrawals (including 'withdrawPartialUnstaking')
staking_withdrawals AS (
SELECT
DATE(fe.block_timestamp) AS event_date,
SUM(TRY_TO_NUMBER(instruction.value:parsed.info.amount::STRING) / 1000000) AS total_withdrawn_amount
FROM
solana.core.fact_events fe
JOIN
QueryRunArchived: QueryRun has been archived