adriaparcerisasHow are stake account sizes changing over time? Total SOL staked and average delegated per staker
Updated 2022-07-25
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
›
⌄
--8. Skip rate + vote credits per validator and corresponding delegator behavior: do delegators respond/change their behavior (undelegate) when validators have bad performance?
--9. Average/median/etc stake amount per validator
--10. Are validators who’ve been down for days, weeks, months still receiving delegations? How much? Of what size?
--11. What does a validator's stake look like over time (increase, decrease, splits to many validators, consolidates to one validator)?
--12. How are stake account sizes changing over time? Is this true across all or just some validators?
--13. Nakamoto coefficient over time (and general stake distribution)
with base as (select
trunc(block_timestamp,'month') as months,
signers[0] as validators,
--stake_authority,
--vote_account,
sum(post_tx_staked_balance/1e9 - pre_tx_staked_balance/1e9) as sol_delegated
from solana.core.ez_staking_lp_actions
where post_tx_staked_balance > pre_tx_staked_balance
and event_type = 'delegate'
and succeeded = 'TRUE'
and block_timestamp >= '2022-01-01'
group by 1,2),
base2 as (select
trunc(block_timestamp,'month') as months,
signers[0] as validators,
--withdraw_destination,
--vote_account,
sum(withdraw_amount/1e9) as sol_withdrawn
from solana.core.ez_staking_lp_actions
where event_type = 'withdraw'
and block_timestamp >= '2022-01-01'
and succeeded = 'TRUE'
group by 1,2),
base3 as(select ifnull(a.months, b.months) as months,
ifnull(a.validators, b.validators) as validator,
--ifnull(a.stake_authority, b.withdraw_destination) as stake_authoritys,
Run a query to Download Data