CODEWITHSUNDEEP
sqldatabaseblockchainnearprotocolflipside
Raven
Raven

Reputation: 1

how can i retrieve the number of validators on NEAR blockchain using SQL query?

As you know the NEAR blockchain is based on Proof of stake protocol.the number of validators can be based on validators 'staking' or even 'not staking'. i'm new to this and i don't know which culoolumnsmns or tables should i use to find this number. should i use tx_hash or tx_reciever? i'm totally confused

WITH 
  staking AS (
    SELECT tx_hash
    FROM near.core.fact_actions_events_function_call
    WHERE method_name IN ('deposit_and_stake', 'stake', 'stake_all')
  ),
  stakes AS (
    SELECT 
      block_timestamp,
      tx_hash AS tx,
      tx_receiver AS validator, 
      tx_signer AS delegator,
      tx:actions[0]:FunctionCall:deposit/pow(10, 24) AS near_staked
    FROM near.core.fact_transactions
    WHERE tx_hash IN (SELECT * FROM staking)
  ),
  monthly AS (
    SELECT 
      trunc(block_timestamp, 'month') AS months,
      tx,
      validator,
      near_staked
    FROM stakes
    WHERE near_staked IS NOT NULL 
  ),
  totals AS (
    SELECT
      months,
      sum(near_staked) AS month_near_staked,
      sum(month_near_staked) OVER (ORDER BY months) AS total_near_staked
    FROM monthly
    GROUP BY 1
    ORDER BY 1
  ),
  ranking AS (
    SELECT 
      months,
      validator,
      count(DISTINCT tx) AS txs,
      sum(near_staked) AS total_near_delegated,
      sum(total_near_delegated) OVER (PARTITION BY validator ORDER BY months) AS cumulative_near_delegated
    FROM monthly 
    GROUP BY 1, 2
  )
SELECT COUNT(DISTINCT validator) AS num_validators
FROM ranking;

Running this query will provide the number of distinct validators present in the provided database information based on the staking activities analyzed. but i don't think it's true

Upvotes: 0

Views: 145

Answers (1)

Vikas Pandey
Vikas Pandey

Reputation: 379

Validators are the nodes in the network that verify and validate new transactions and blocks. the number of validators is not typically found in transaction-related data such as tx_hash or tx_receiver.

To find the number of validators, you would need to look at the consensus protocol details and the list of current validators, it can be obtained from the blockchain data, but not directly from individual transaction data.

Try this, it might help ya:

https://explorer.near.org/nodes/validators

enter image description here

Upvotes: 1

Related Questions