Analytics eth feeds: Difference between revisions
From Chainlink Community Wiki
No edit summary |
No edit summary |
||
| Line 26: | Line 26: | ||
* https://www.w3schools.com/sql/default.asp | * https://www.w3schools.com/sql/default.asp | ||
* https://ethereum.org/en/developers/docs | * https://ethereum.org/en/developers/docs | ||
---- | |||
==='''Getting a list of all Chainlink OCR contracts:'''=== | |||
In this subquery, generate a list of all contracts that a set of well-known node operators are transmitting to within the '''''ethereum."transactions"''''' table. In this case, the node operators chosen are Chainlayer, DexTrac, Fiews and Linkpool. The '''''substring''''' filter of ''''''\xc9807539'''''' is used to only retrieve transactions from the nodes with the Transmit method. Since each node operator will interact with a contract multiple times, only one occurrence of the contract address is needed in the final list. The '''''all_ocr_contracts''''' alias (can be changed to any desired name) will hold list of contracts for later use in the query. | In this subquery, generate a list of all contracts that a set of well-known node operators are transmitting to within the '''''ethereum."transactions"''''' table. In this case, the node operators chosen are Chainlayer, DexTrac, Fiews and Linkpool. The '''''substring''''' filter of ''''''\xc9807539'''''' is used to only retrieve transactions from the nodes with the Transmit method. Since each node operator will interact with a contract multiple times, only one occurrence of the contract address is needed in the final list. The '''''all_ocr_contracts''''' alias (can be changed to any desired name) will hold list of contracts for later use in the query. | ||
| Line 48: | Line 47: | ||
) | ) | ||
</pre></small> | </pre></small> | ||
---- | |||
==='''Getting a list of Chainlink OCR contracts for a single node:'''=== | |||
As in the above section, generate a list of all contracts that a single node operator is transmitting to within the '''''ethereum."transactions"''''' table. In this case, the node operator chosen is DexTrac. The '''''substring''''' filter of ''''''\xc9807539'''''' is again used to only retrieve transactions from the nodes with the Transmit method. Since the node operator will interact with a contract multiple times, only one occurrence of the contract address is needed in the final list. The '''''dt_ocr_contracts''''' alias (can be changed to any desired name) will hold list of contracts for later use in the query. | As in the above section, generate a list of all contracts that a single node operator is transmitting to within the '''''ethereum."transactions"''''' table. In this case, the node operator chosen is DexTrac. The '''''substring''''' filter of ''''''\xc9807539'''''' is again used to only retrieve transactions from the nodes with the Transmit method. Since the node operator will interact with a contract multiple times, only one occurrence of the contract address is needed in the final list. The '''''dt_ocr_contracts''''' alias (can be changed to any desired name) will hold list of contracts for later use in the query. | ||
| Line 69: | Line 66: | ||
</pre></small> | </pre></small> | ||
</p> | </p> | ||
---- | |||
Revision as of 20:08, 27 May 2022
Quick Take: Ethereum OCR Feed Queries
 |
This query allows us to visualize Chainlink OCR feed activity and compare a single node operator's performance against the whole. |
Ethereum OCR Feed Queries
This query allows us to visualize Chainlink OCR feed activity and compare a single node operator's performance against the whole. Lets break down the query to understand the data it is retrieving.
Full query can be found here: https://dune.com/queries/843986
NOTE: Before continuing with the break down, it is recommended that you have a basic understanding of SQL language queries and how to interact with Ethereum smart contracts.
Helpful Resources:
Getting a list of all Chainlink OCR contracts:
In this subquery, generate a list of all contracts that a set of well-known node operators are transmitting to within the ethereum."transactions" table. In this case, the node operators chosen are Chainlayer, DexTrac, Fiews and Linkpool. The substring filter of '\xc9807539' is used to only retrieve transactions from the nodes with the Transmit method. Since each node operator will interact with a contract multiple times, only one occurrence of the contract address is needed in the final list. The all_ocr_contracts alias (can be changed to any desired name) will hold list of contracts for later use in the query.
all_ocr_contracts AS (
SELECT
DISTINCT("to")
FROM
ethereum."transactions"
WHERE
"from" in (
'\xb976d01275b809333e3efd76d1d31fe9264466d0', -- DexTrac
'\xc74cE67BfC623c803D48AFc74a09A6FF6b599003', -- Chainlayer
'\x218B5a7861dBf368D09A84E0dBfF6C6DDbf99DB8', -- Fiews
'\xcC29be4Ca92D4Ecc43C8451fBA94C200B83991f6' -- LinkPool
)
AND substring("data":: bytea FROM 0 FOR 5):: bytea = '\xc9807539'
)
Getting a list of Chainlink OCR contracts for a single node:
As in the above section, generate a list of all contracts that a single node operator is transmitting to within the ethereum."transactions" table. In this case, the node operator chosen is DexTrac. The substring filter of '\xc9807539' is again used to only retrieve transactions from the nodes with the Transmit method. Since the node operator will interact with a contract multiple times, only one occurrence of the contract address is needed in the final list. The dt_ocr_contracts alias (can be changed to any desired name) will hold list of contracts for later use in the query.
dt_ocr_contracts AS (
SELECT
DISTINCT("to")
FROM
ethereum."transactions"
WHERE
"from" in (
'\xb976d01275b809333e3efd76d1d31fe9264466d0' -- DexTrac
)
AND substring("data":: bytea FROM 0 FOR 5):: bytea = '\xc9807539'
)