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

select

count(distinct node_pubkey) as n_validators,

sum(active_stake) as total_staked_sol,

sum(active_stake) / count(node_pubkey) as validator_avg_stake_sol

from validators_info

where active_stake > 0

;