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
    )

---

Creating final table:

In the final subquery, data from the previous subqueries are combined in a single table that can be used to create visualizations.

• SUM("count_ocr_contracts"): total number of active OCR feeds
• SUM("ocr_runs"): total number of transactions from all OCR contracts
• SUM("dt_count_ocr_contracts"): total number of active OCR feeds the node operator participates in
• SUM("dt_ocr_runs"): total number of transactions from OCR contracts the node operator participates in
• SUM("dt_only_ocr_success_runs"): total successful number of transactions from the node operator
• ROUND(SUM("dt_count_ocr_contracts")/SUM("count_ocr_contracts") *100, 2): calculate percentage of feeds node operator is on
• CASE WHEN SUM("dt_ocr_runs") = 0 THEN NULL ELSE ROUND(SUM("dt_only_ocr_success_runs") / SUM("dt_ocr_runs") * 100, 2) END: calculate percentage of transmits from node operator
• CASE WHEN SUM("dt_only_ocr_success_runs" + "dt_only_ocr_failed_runs") = 0 THEN NULL ELSE ROUND(SUM("dt_only_ocr_success_runs") / SUM("dt_only_ocr_success_runs" + "dt_only_ocr_failed_runs") * 100, 2) END: calculate percentage of successful transmits performed by node operator

    SELECT
        "tx_date",
        SUM("count_ocr_contracts") AS "total_ocr_contracts",
        SUM("ocr_runs") AS "ocr_transmits",
        SUM("dt_count_ocr_contracts") AS "total_dt_contracts",
        SUM("dt_ocr_runs") AS "dt_ocr_trasmits",
        SUM("dt_only_ocr_success_runs") AS "dt_only_ocr_transmits",
        ROUND(SUM("dt_count_ocr_contracts")/SUM("count_ocr_contracts") *100, 2) AS "dt_ocr_percentage",
        CASE WHEN SUM("dt_ocr_runs") = 0 THEN NULL ELSE ROUND(SUM("dt_only_ocr_success_runs") / SUM("dt_ocr_runs") * 100, 2) END AS "dt_transmit_percentage",
        CASE WHEN SUM("dt_only_ocr_success_runs" + "dt_only_ocr_failed_runs") = 0 THEN NULL ELSE ROUND(SUM("dt_only_ocr_success_runs") / SUM("dt_only_ocr_success_runs" + "dt_only_ocr_failed_runs") * 100, 2) END AS "dt_success_percentage"
    FROM
        all_ocr_transmits a
    INNER JOIN dt_ocr_transmits dt on a.tx_date = dt.dt_tx_date
    GROUP BY
        "tx_date"
    ORDER BY
        "tx_date" DESC