Finding stake and unstake transactions

Linear protocol address on Near blockchain: linear-protocol.near.

To track stake and unstake transactions, we use data from [near.core.fact_receipts].

A stake transaction has the following specifications:

RECEIVER_ID = 'linear-protocol.near' AND actions:receipt:Action:actions[0]:FunctionCall:method_name = 'deposit_and_stake'

An unstake transaction has the following specifications:

RECEIVER_ID = 'linear-protocol.near' AND actions:receipt:Action:actions[0]:FunctionCall:method_name IN ('unstake_all', 'unstake', 'instant_unstake')

For other platforms(Meta Pool and Stader), the calculations are similar to those described above, except that their RECEIVER_ID and actions:receipt:Action:actions[0]:FunctionCall:method_name are different.

For calculations related to Meta Pool and Stader, we used Yousefi_1994's queries.

The following SQL code obtains all stake and unstake transactions on the LiNEAR platform:

WITH STAKED_AMOUNT AS (
SELECT BLOCK_TIMESTAMP,TX_HASH ,actions:predecessor_id AS "Address",
((actions:receipt:Action:actions[0]:FunctionCall:deposit)) / POW(10,24) AS "Amount",
 'Stake' AS "Type"
 FROM near.core.fact_receipts 
WHERE RECEIVER_ID = 'linear-protocol.near' AND
actions:receipt:Action:actions[0]:FunctionCall:method_name = 'deposit_and_stake'
),
UNSTAKE AS (
SELECT BLOCK_TIMESTAMP,TX_HASH ,actions:predecessor_id AS "Address", 
(REPLACE(SPLIT(SPLIT(ARRAY_TO_STRING(LOGS, ','), ':')[7], ',')[0], '"','')) / POW(10,24) * -1  AS "Amount",
'Unstake' AS "Type"
FROM near.core.fact_receipts
 WHERE RECEIVER_ID = 'linear-protocol.near' AND
 actions:receipt:Action:actions[0]:FunctionCall:method_name IN ('unstake_all', 'unstake')  AND 
 STATUS_VALUE:Failure IS NULL
),
INSTANT_UNSTAKE AS (
SELECT BLOCK_TIMESTAMP,TX_HASH ,actions:predecessor_id AS "Address",
(REPLACE(SPLIT(SPLIT(LOGS[1], ':')[7],',')[0], '"','')) / POW(10,24) * -1  AS "Amount",
'Unstake' AS "Type"
FROM near.core.fact_receipts
 WHERE RECEIVER_ID = 'linear-protocol.near' 
AND actions:receipt:Action:actions[0]:FunctionCall:method_name  = 'instant_unstake'  AND 
 STATUS_VALUE:Failure IS NULL
),
UNSTAKED_AMOUNT AS (
SELECT * FROM UNSTAKE
UNION ALL
SELECT * FROM INSTANT_UNSTAKE
),
STAKE_AND_UNSTAKE_AMOUNT AS (
SELECT * FROM UNSTAKED_AMOUNT
UNION ALL
SELECT * FROM STAKED_AMOUNT
),
NEAR_PRICE AS (
SELECT TIMESTAMP::DATE AS DAYS, AVG(PRICE_USD) AS PRICE
FROM near.core.fact_prices 
WHERE SYMBOL = 'wNEAR' AND DAYS >= (SELECT MIN(BLOCK_TIMESTAMP) FROM STAKE_AND_UNSTAKE_AMOUNT)
GROUP BY DAYS
)
SELECT M.BLOCK_TIMESTAMP::DATE AS DAYS, M.TX_HASH, "Address", "Amount", "Type"
FROM STAKE_AND_UNSTAKE_AMOUNT M LEFT JOIN NEAR_PRICE ON (M.BLOCK_TIMESTAMP::DATE = NEAR_PRICE.DAYS ) LEFT JOIN near.core.fact_transactions TR USING(TX_HASH)
WHERE TR.TX_STATUS = 'Success'

NEAR - 23. NEAR Protocol or Project Dashboard