TotalStake AS (

SELECT EPOCH, SUM(ACTIVE_STAKE) as TOTAL_STAKE

FROM solana.gov.fact_validators

WHERE active_stake > 0 and delinquent = False

GROUP BY EPOCH

),

TotalCountryStake AS (

SELECT EPOCH,

SPLIT_PART(DATA_CENTER_KEY, '-', 2) as COUNTRY,

SUM(ACTIVE_STAKE) as TOTAL_STAKE

FROM solana.gov.fact_validators

WHERE active_stake > 0 and delinquent = False

GROUP BY EPOCH, COUNTRY

),

TotalDataCenterStake AS (

SELECT EPOCH,

DATA_CENTER_KEY,

SUM(ACTIVE_STAKE) as TOTAL_STAKE

FROM solana.gov.fact_validators

WHERE active_stake > 0 and delinquent = False

GROUP BY EPOCH, DATA_CENTER_KEY

),

RankedNodes AS (

SELECT EPOCH, NODE_PUBKEY, ACTIVE_STAKE,

ROW_NUMBER() OVER(PARTITION BY EPOCH ORDER BY ACTIVE_STAKE DESC) as RANK,

SUM(ACTIVE_STAKE) OVER(PARTITION BY EPOCH ORDER BY ACTIVE_STAKE DESC) AS ACCUMULATED_STAKE

FROM solana.gov.fact_validators

WHERE active_stake > 0 and delinquent = False

),

RankedCountries AS (

SELECT tcs.EPOCH, tcs.COUNTRY, tcs.TOTAL_STAKE,

ROW_NUMBER() OVER(PARTITION BY tcs.EPOCH ORDER BY tcs.TOTAL_STAKE DESC) as RANK,

SUM(tcs.TOTAL_STAKE) OVER(PARTITION BY tcs.EPOCH ORDER BY tcs.TOTAL_STAKE DESC) AS ACCUMULATED_STAKE

FROM TotalCountryStake tcs

),