flyingfishAPI - Validators List
Updated 2023-08-29
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
›
⌄
-- forked from API - SolBlaze Active Validators @ https://flipsidecrypto.xyz/edit/queries/cbe594fa-3289-4da2-82da-6cd096c111de
-- Gets the average APY of BlazeStake over the past few epochs
WITH solblaze_validators AS (
SELECT livequery.live.udf_api('https://stake.solblaze.org/api/v1/validator_set') AS resp
),
cte AS (
SELECT
value as vote_accounts
FROM solblaze_validators, lateral flatten (input => resp:data:vote_accounts)
),
cte1 AS (
SELECT
epoch
, vote_pubkey
, node_pubkey
, active_stake / pow(10, 9)
, commission
, created_at
, delinquent
, epoch_active
, epoch_credits
, keybase_id
, validator_name
, www_url
, row_number() over (partition BY vote_pubkey ORDER BY epoch DESC) as rn
FROM solana.gov.fact_validators
WHERE vote_pubkey IN (SELECT vote_accounts FROM cte)
)
SELECT * FROM cte1
where rn = 1
ORDER BY vote_pubkey, epoch DESC, rn DESC
-- HAVING rn < 3
-- PIVOT(max(value) for key IN ('total'))
Run a query to Download Data