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

, latitude

, longitude

from solana.gov.fact_validators

qualify row_number() over (partition by node_pubkey order by epoch desc) = 1

and not delinquent

and not active_stake is null

),

stakes as (

select

epoch

, node_pubkey

, validator_name

, latitude

, longitude

, active_stake

, active_stake / sum(active_stake) over () * 100 as stake_pct

, sum(active_stake) over (order by active_stake desc) / sum(active_stake) over () * 100 as stake_pct_cumul

from validators_info

where active_stake > 0

),