LONG_TERM_HOLDERS | |
|---|---|
1 | 512155 |
jupdevrelLong Term Holders
Updated 3 days ago
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
›
⌄
WITH staker_first_event AS (
SELECT
SIGNERS[0]::STRING AS wallet,
MIN(block_timestamp::date) AS first_stake_date
FROM solana.core.ez_events_decoded
WHERE program_id = 'voTpe3tHQ7AjQHMapgSue2HJFAh2cGsdokqN3XqmVSj'
AND succeeded = 'true'
AND event_type = 'increaseLockedAmount'
GROUP BY SIGNERS[0]::STRING
),
old_stakers AS (
SELECT wallet
FROM staker_first_event
WHERE first_stake_date < DATEADD(month, -6, CURRENT_DATE)
),
recent_withdrawals AS (
SELECT DISTINCT ed.SIGNERS[0]::STRING AS wallet
FROM solana.core.fact_events fe
JOIN solana.core.ez_events_decoded ed
ON fe.tx_id = ed.tx_id
AND fe.program_id = ed.program_id
CROSS JOIN LATERAL FLATTEN(input => TRY_PARSE_JSON(fe.inner_instruction):instructions) AS instruction
WHERE fe.program_id = 'voTpe3tHQ7AjQHMapgSue2HJFAh2cGsdokqN3XqmVSj'
AND fe.succeeded = 'true'
AND ed.event_type IN ('withdraw', 'withdrawPartialUnstaking')
AND fe.block_timestamp::date >= DATEADD(month, -6, CURRENT_DATE)
)
SELECT COUNT(*) AS long_term_holders
FROM old_stakers os
LEFT JOIN recent_withdrawals rw ON os.wallet = rw.wallet
WHERE rw.wallet IS NULL;
Last run: 3 days ago
1
10B
20s