with validators as (SELECT

distinct(pool_address) as validator

from near.core.dim_staking_actions

),

stakes as (select

tx_hash,

block_timestamp,

receiver_id,

regexp_substr_all(to_varchar(logs), 'is(.{50})') as balance_array,

balance_array[array_size(balance_array)-1] as dirty_amount,

regexp_substr(dirty_amount, '[0-9]+') as clean_amount,

clean_amount::int/1e24 as total_staked

from near.core.fact_receipts

where receiver_id in (select validator from validators)

and receipt_index = '0'

and contains(to_varchar(logs), 'Contract total staked balance is')),

max_time as (select

receiver_id as validator,

max(block_timestamp) as max

from stakes

group by receiver_id

),

max_time_balance as (

select m.*,

s.total_staked,

sum(s.total_staked) over (order by s.total_staked desc rows between unbounded preceding and current row) as cum_stake,

sum(s.total_staked) over () as total_stake,

total_staked/total_stake as voting_power,

cum_stake/total_stake as cum_voting_power

from max_time m, stakes s

where m.max = s.block_timestamp

and m.validator = s.receiver_id),