M: native stakes(2)

elovianooM: native stakes(2)

Updated 2025-01-25

with native_stakes as (

select *

from (

select stake_account

, case when validator_name like 'validator:%' then SPLIT_PART(validator_name, ':', -1) else validator_name end AS validator_name

, vote_account

, stake_active

, post_tx_staked_balance

, post_tx_staked_balance_usd

-- , max_by(stake_active , (block_timestamp , index , inner_index)) , max_by(POST_TX_STAKED_BALANCE , block_timestamp)

, row_number() over (partition by stake_account order by block_timestamp desc , index desc , inner_index desc) as recency

from solana.marinade.ez_native_staking_actions

) s

where s.recency = 1

-- group by 1

)

-- QA

select stake_Account , count(*)

from native_stakes

group by 1

having count(*) > 1

-- select *

-- from native_stakes

-- where stake_active = 'false' and post_tx_staked_balance < 1

-- where validator_name = '6m4ZwGp8zCuqjBoAn11pYAQun2FwSMe86RwPbqpf34YC'

select validator_name , vote_account

, case

when stake_active = 'true' then 'active'

when stake_active = 'false' or post_tx_staked_balance = 0 then 'inactive'

when stake_active is null then 'initialised'

Last run: 3 months ago

	VALIDATOR_NAME	VOTE_ACCOUNT	STAKE_STATUS	STAKE_ACCOUNTS	STAKED_SOL	STAKES_USD
1	8vyuJTHSDkx7k1zymea4TMsgvixf3rCYBXHPDQajePkE	8vyuJTHSDkx7k1zymea4TMsgvixf3rCYBXHPDQajePkE	inactive	4268	0	0
2	4v2os15BGgAnqiy76YvPmAywp5xKh9jfBPfnQGzUVc9c	4v2os15BGgAnqiy76YvPmAywp5xKh9jfBPfnQGzUVc9c	inactive	15625	29.142026774	7009.7
3	D7ZCDE1PHe8duMjNpxwHrYbrRzcnsS7p4nD2daLzWwtr	D7ZCDE1PHe8duMjNpxwHrYbrRzcnsS7p4nD2daLzWwtr	inactive	5160	37.611254461	9426.83
4	Luck3DN3HhkV6oc7rPQ1hYGgU3b5AhdKW9o1ob6AyU9	Luck3DN3HhkV6oc7rPQ1hYGgU3b5AhdKW9o1ob6AyU9	inactive	4824	2.058212195	224.75
5			inactive	1143	0	0
6	VoTEJDVw84uZvDWcMXYgfNAVcLETHsPyPEc2nTpwZPa	VoTEJDVw84uZvDWcMXYgfNAVcLETHsPyPEc2nTpwZPa	inactive	2248	0	0
7	4bevysucyvnll6z1ybhh8kh5fnahjcbx5gyhn5dfz1fe	FJ7sKbuXR28w4K5EChBbi9tXYfCfrdpF4EaKnRvLPzL8	inactive	646	0	0
8	genesis lab	EFEKcHrUBsRoQkuTSJAQNZnj1u8h9oE4LoCYerednc3F	inactive	1666	0	0
9	21wUViiyG1g47VZ39ZZsSkFX9nu6bkyfy6jryHGD2TUB	21wUViiyG1g47VZ39ZZsSkFX9nu6bkyfy6jryHGD2TUB	active	2418	2595.607067028	603377.05
10	7zFvisPXq7xCFoSAL4oxZhRpDonmf8NxQFuLNGW4UYFh	7zFvisPXq7xCFoSAL4oxZhRpDonmf8NxQFuLNGW4UYFh	active	191	4181.857746152	971166.24
11	ag 🔥🔥🔥 0% in april-may	F5b1wSUtpaYDnpjLQonCZC7iyFvizLcNqTactZbwSEXK	inactive	111	0	0
12	huva	DTWh2VQuUJmskHuZWy1KLNvzKqk7PeMkCoaNvZy415U7	inactive	157	0	0
13	3cstv2qsp2whfvojhhr4xbgcgfrerzfttspgydp3ujki	A8q1whmfdNKCNN2Y4Yjc4cnc4esCBcwXrcaWnYwrH4r6	inactive	1407	0	0
14	RSSAw6n7Tvqi5iJc2xvHMfk1bhx47GVqj2pj1bq5NUq	RSSAw6n7Tvqi5iJc2xvHMfk1bhx47GVqj2pj1bq5NUq	inactive	118	0	0
15	3razlze6gqvdahdqtkjfq8ewpamzpwgkljp3tzkd5ivh	84gebYpPpEafPeGJUVA8QzfaTQC3GeyVufCTHpqsQqE2	inactive	20	10.50704854	2776.82
16	tri1cHBy47fPyhCvrCf6FnR7Mz6XdSoSBah2FsZVQeT	tri1cHBy47fPyhCvrCf6FnR7Mz6XdSoSBah2FsZVQeT	inactive	3985	10.595438166	1506.79
17	BrRf2kyJEuW8TgdeDjvJcKK4NzTzRtM9RB6WuVKXHxkN	BrRf2kyJEuW8TgdeDjvJcKK4NzTzRtM9RB6WuVKXHxkN	active	2427	9513.597185724	1671434.12
18	GioetmC79nLRnN7VDfHaq8coWAEFPJKu9py59uUqdV5U	GioetmC79nLRnN7VDfHaq8coWAEFPJKu9py59uUqdV5U	inactive	25	0	0
19	ushakov	J6hFEnGhsGxTyss6DNW9aNQskPZ98gD8P6Wo1F5MBsCK	inactive	27	0	0
20	8W4JH1JgFuB6qY6En5zyVZH1qLAr2hafCaXGFZaKpRFm	8W4JH1JgFuB6qY6En5zyVZH1qLAr2hafCaXGFZaKpRFm	inactive	4	0	0

...

983

115KB

flipside

flipside