CarlOwOsmarinade unstake
Updated 2022-12-01
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
›
⌄
with unstaking_data as (
SELECT
*
FROM
solana.core.fact_events
WHERE
block_timestamp >= '2022-10-01'
and instruction:programId = 'MarBmsSgKXdrN1egZf5sqe1TMai9K1rChYNDJgjq7aD'
and inner_instruction:instructions[0]:parsed:info:source = 'UefNb6z6yvArqe4cJHTXCqStRsKmWhGxnZzuHbikP5Q' ),
unstaking_users as (
SELECT
ftt.block_timestamp,
signers as ug_users,
ftt.tx_id
FROM
solana.core.fact_transactions ftt
JOIN unstaking_data ud ON ftt.tx_id = ud.tx_id
)
SELECT
trunc(a.block_timestamp, 'day') as date,
CASE
WHEN a.block_timestamp <= '2022-11-07' THEN 'Before Market Downturn'
ELSE 'After Market Downturn'
END AS label,
sum(inner_instruction:instructions[0]:parsed:info:lamports / 1e9) as total_unstaked,
count (distinct ug_users) as n_unstakers_daily
FROM
unstaking_data a
JOIN unstaking_users b ON a.tx_id = b.tx_id
WHERE
date >= '2022-10-01'
GROUP BY
1, 2
Run a query to Download Data