0

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

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Raven
  • 1
  • 1
  • 1
    Please tag your RDBMS and share your input and expected output. – Amira Bedhiafi Jul 02 '23 at 20:06
  • u can find data in here: https://flipsidecrypto.xyz/edit/queries/9eb0c642-e597-4f81-b865-3987dba8075e i need to find the number of validators and seat price at the moment and also in the past 30 days – Raven Jul 02 '23 at 21:43
  • This is a support question for Flipside Crypto which is a commercial market data provider. What kind of data structures they use is internal matter for Flipside Crypto and it is unlikely anyone outside Flipside Crypto support team can help you. – Mikko Ohtamaa Jul 03 '23 at 11:05

1 Answers1

1

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

Vikas Pandey
  • 197
  • 7
  • thanks. but i need to code an SQL query on a web-based DBMS and i don't have a clue how to filter data to find the number of validators. i've checked near.org and i can see it there.but i want to analyze data in the past 30 days,so.... – Raven Jul 02 '23 at 21:42