tab1 AS (

SELECT

trunc(block_timestamp,'month') as date,

address as validator,

median(balance) as "NEAR Staked",

sum("NEAR Staked") over (partition by date order by "NEAR Staked" DESC) as cumulative_near_month,

row_number() over (partition by date order by "NEAR Staked" DESC) as rank

FROM near.gov.fact_staking_pool_balances

GROUP BY 1,2

ORDER BY 4 ASC

),

tab2 AS (

SELECT

date,

sum("NEAR Staked") as total_near_staked,

max("NEAR Staked") as top_near_validator,

sum(case when rank < 11 then "NEAR Staked" end) as total_near_top_ten, -- amount of near in top 10 validators

total_near_top_ten/total_near_staked*100 as "Top 10 Validator % Share",

top_near_validator/total_near_staked*100 as "Top Validator % Share",

1-round(1 - 2 * sum(("NEAR Staked" * (rank - 1) + "NEAR Staked" / 2)) / count(*) / sum("NEAR Staked"), 4) AS GINI, -- daily gini

COUNT(DISTINCT validator) as "Validators Count"

FROM tab1

GROUP BY 1

select * from tab2 where date<trunc(current_date,'month')

order by 1 DESC