SELECT

DISTINCT t.tx_hash

, t.block_timestamp as dtime

, method_name

, t.tx_status

, logs

, outcome

, coalesce(logs[2], logs[1]) as line

, TO_NUMBER(regexp_substr(line, 'Contract total staked balance is (\\d+)', 1, 1, 'e', 1))/1e24 as balance

, t.tx_signer

, r.receiver_id

, rank() OVER (PARTITION by r.receiver_id ORDER BY dtime DESC) as rank

FROM near.core.fact_actions_events_function_call a

JOIN near.core.fact_transactions t ON a.tx_hash = t.tx_hash

JOIN near.core.fact_receipts r ON a.tx_hash = r.tx_hash

WHERE 1=1

AND method_name IN('deposit_and_stake','unstake_all', 'stake', 'unstake')

AND coalesce(logs[2], logs[1]) LIKE ('Contract total staked%')

AND t.tx_status = 'Success'

qualify rank=1

),

refine as (

SELECT

balance

, receiver_id

, SUM(balance) OVER() as total_balance

, rank() OVER (ORDER BY balance DESC) as validator_rank

, SUM(balance) OVER (ORDER BY balance DESC) as cum_balance

, ROUND( (cum_balance/total_balance)*100, 2) as cum_bal_perc

from latest

WHERE balance>20378 --current min. stake requirement? https://near-staking.com/