flyingfishValidator List copy
    Updated 2024-04-10
    -- forked from mar1na-catscatscode / Validator List @ https://flipsidecrypto.xyz/mar1na-catscatscode/q/JClJ-2cogXm1/validator-list

    -- Credit to marqu: https://flipsidecrypto.xyz/marqu/q/y7paTrjuvUdX/solana-stake---active-validators

    with

    validators_info as (

    select

    epoch
    , node_pubkey
    , active_stake / pow(10, 9) as active_stake
    , coalesce(concat(validator_name, ' [', left(node_pubkey, 5), '...]'), concat(left(node_pubkey, 5), '...', right(node_pubkey, 5))) as validator_name
    , software_version
    , data_center_key
    , regexp_replace(data_center_key, '^\\d+-|-.+$') as country
    , longitude
    , latitude

    from solana.gov.fact_validators
    -- qualify row_number() over (partition by node_pubkey order by epoch desc) = 1
    where epoch = (select max(epoch) ::int from solana.gov.fact_validators)
    and not delinquent
    and not active_stake is null
    ),

    stakes as (

    select
    epoch
    , node_pubkey
    , validator_name
    , active_stake
    , active_stake / sum(active_stake) over () * 100 as stake_pct
    QueryRunArchived: QueryRun has been archived