Analytics eth feeds

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.

Full query can be found here: https://dune.com/queries/843986

Lets break down the query to understand the data it is retrieving. 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:

• https://www.w3schools.com/sql/default.asp
• 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.

    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'
      )