SELECT

date_trunc('month',date) as month,

address,

balance as cumulative_NEAR_stacked,

row_number() over (partition by date order by cumulative_NEAR_stacked DESC) as rank

from near.core.fact_staking_pool_daily_balances

group by 1,2

),monthly_total_stack AS (

SELECT

date_trunc('month',date) as month_total,

sum(balance) as cumulative_total_NEAR_stacked

FROM near.core.fact_staking_pool_daily_balances

GROUP BY 1

),

nakamoto AS (

SELECT

*,

cumulative_total_NEAR_stacked*0.51 as percent_stake_51,

case when percent_stake_51 > cumulative_NEAR_stacked then 1 else 0 end as nakamoto_metric

FROM monthly_stack

LEFT JOIN monthly_total_stack ON month = month_total

ORDER BY month_total, rank

),

nakamoto_coefficient AS (

SELECT

month_total,

sum(nakamoto_metric) as Nakamoto_Coefficient,

sum(nakamoto_metric)/count(nakamoto_metric)*100 as Validator_over_51_percente

FROM nakamoto

GROUP BY 1

select * from nakamoto_coefficient