marquMarinade Growth - Staking actions breakdown bars granular
Updated 2022-11-23
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
›
⌄
with
delayed_unstake as (
select
block_timestamp,
tx_id,
f.value :parsed :info :amount ::int as amount
from solana.core.fact_events events
inner join lateral flatten (input => inner_instruction :instructions) f
inner join solana.core.fact_stake_pool_actions spool
using(tx_id,block_timestamp,succeeded)
where succeeded
and block_timestamp ::date > current_date() - interval '{{period_months}} months'
and spool.action = 'order_unstake'
and spool.stake_pool_name = 'marinade'
and f.value :parsed :type ::string = 'burn'
and f.value :parsed :info :authority ::string = spool.address
),
pool_actions as (
select
block_timestamp,
stake_pool_name,
case
when action in ('deposit','deposit_dao','deposit_stake','deposit_dao_stake','deposit_dao_with_referrer') then 'DEPOSIT'
else 'WITHDRAW'
end as action_label,
case when action_label = 'DEPOSIT' then coalesce(spool.amount,dstake.amount) / pow(10,9)
else coalesce(spool.amount,dstake.amount) / pow(10,9) * (-1)
end as amount,
address as user_address,
Run a query to Download Data