Analytics eth feeds

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:

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

---

Retrieving all transactions from OCR contracts:

This subquery retrieves transaction data from the all_ocr_contracts subquery. Results are filtered for only the Transmit method transactions and block_time >= '2020-08-08' helps optimize the query by providing a starting date to search from. Final results are grouped by the month, GROUP BY tx_date. Aliases can be named as desired.

• date_trunc('month', block_time): condenses the block time timestamps for the transaction data by month
• COUNT(DISTINCT("to")): generates a count of the individual OCR contracts
• COUNT(CASE WHEN success = true THEN 1 END): generates a count of the total OCR transmit transactions that succeeded

    all_ocr_transmits AS (
        SELECT
          date_trunc('month', block_time) AS tx_date,
          COUNT(DISTINCT("to")) AS count_ocr_contracts,
          COUNT(CASE WHEN success = true THEN 1 END) AS ocr_runs
        FROM
          ethereum."transactions"
        WHERE
          "to" in (SELECT * FROM all_ocr_contracts)
          AND substring("data":: bytea FROM 0 FOR 5):: bytea = '\xc9807539'
          AND block_time >= '2020-08-08'
        GROUP BY tx_date
      )

---

Retrieving all transactions from OCR contracts for a single node:

Similar to the previous section except using contract list from the dt_ocr_contracts subquery.

• COUNT(CASE WHEN success = true AND "from" = '\xb976d01275b809333e3efd76d1d31fe9264466d0' THEN 1 END): generates a count of OCR transmit transactions done by the node operator that succeeded
• COUNT(CASE WHEN success = false AND "from" = '\xb976d01275b809333e3efd76d1d31fe9264466d0' THEN 1 END): generates a count of OCR transmit transactions done by the node operator that failed

  dt_ocr_transmits AS (
    SELECT
      date_trunc('month', block_time) AS dt_tx_date,
      COUNT(DISTINCT("to")) AS dt_count_ocr_contracts,
      COUNT(CASE WHEN success = true THEN 1 END) AS dt_ocr_runs,
      COUNT(CASE WHEN success = true AND "from" = '\xb976d01275b809333e3efd76d1d31fe9264466d0' THEN 1 END) AS dt_only_ocr_success_runs,
      COUNT(CASE WHEN success = false AND "from" = '\xb976d01275b809333e3efd76d1d31fe9264466d0' THEN 1 END) AS dt_only_ocr_failed_runs
    FROM
      ethereum."transactions"
    WHERE
      "to" in (SELECT * FROM dt_ocr_contracts)
      AND substring("data":: bytea FROM 0 FOR 5):: bytea = '\xc9807539'
      AND block_time >= '2020-08-08'
    GROUP BY dt_tx_date
  )

---