SELECT

epoch,

SUM(active_stake/pow(10,9)) as epoch_total_staked

FROM solana.gov.fact_validators

WHERE active_stake > 0 AND delinquent = False

GROUP BY epoch

),

CumulativeStake AS (

SELECT

f.epoch,

f.node_pubkey,

f.active_stake/pow(10,9) as total_staked,

SUM(f.active_stake/pow(10,9)) OVER (PARTITION BY f.epoch ORDER BY f.active_stake/pow(10,9) DESC) as cumulative_staked,

t.epoch_total_staked

FROM solana.gov.fact_validators f

JOIN TotalStake t ON f.epoch = t.epoch

WHERE f.active_stake > 0 AND f.delinquent = False

SELECT

epoch,

COUNT(node_pubkey) + 1 as nakamoto_coefficient

FROM CumulativeStake

WHERE cumulative_staked < epoch_total_staked / 3

GROUP BY epoch

ORDER BY epoch DESC