SELECT DISTINCT

t.RECEIVER_ID as validator,

FIRST_VALUE(REGEXP_SUBSTR(f2.value, 'contract total staked balance is ([0-9]+)[\.]' , 1, 1, 'ei')::float / 1e24) OVER (PARTITION BY validator ORDER BY block_timestamp DESC) as power

FROM near.core.fact_receipts t, lateral flatten(input => logs) f2

WHERE t.RECEIVER_ID ILIKE '%pool%.near'

AND f2.value ILIKE '%contract total staked balance is%'

ORDER BY 2 DESC

LIMIT 100

),

tot_staked as (

SELECT SUM(power) as all_near_staked FROM active_set

),

lino2 as (

SELECT

validator,

power as staked,

all_near_staked,

ROUND(staked / all_near_staked * 100, 2) as power_percentage,

SUM(power_percentage) OVER (ORDER BY power) as cumulative_stake_perc

FROM active_set, tot_staked

GROUP BY 1, 2, 3

ORDER BY staked

LIMIT 100

SELECT

validator,

staked,

cumulative_stake_perc as "Cumulative Voting Power",

0.01 * ((cumulative_stake_perc + LAG(cumulative_stake_perc, 1, 0) OVER (ORDER BY staked DESC)) / 100) * 0.5 as lorenz_curve,

ROW_NUMBER() OVER (ORDER BY staked) as "Perfect Equality"

FROM lino2

GROUP BY 1, 2, 3

ORDER BY staked