Analytics eth feeds

Ethereum OCR Feed Queries

These are queries to retrieve on-chain stats about Ethereum OCR feeds and node operator transmissions.

Ethereum Mainnet: Total OCR Feeds (counter):

-- **************************************************
-- (1) Within the ethereum."transactions" table, lookup the individual OCR contracts being transmitted to by nodes.
-- (2) Use a random mix of well-known node operators to get all deployed OCR feeds.
-- (3) Filter by transmission transactions.
-- **************************************************
SELECT COUNT(DISTINCT("to"))
FROM ethereum."transactions" -- (1)
WHERE
    "from" in (
        '\xb976d01275b809333e3efd76d1d31fe9264466d0', -- DexTrac
        '\xc74cE67BfC623c803D48AFc74a09A6FF6b599003', -- Chainlayer
        '\x218B5a7861dBf368D09A84E0dBfF6C6DDbf99DB8', -- Fiews
        '\xcC29be4Ca92D4Ecc43C8451fBA94C200B83991f6' -- LinkPool
        ) -- (2)
    AND
    substring("data"::bytea FROM 0 FOR 5)::bytea = '\xc9807539' -- (3)

Ethereum Mainnet: Total Successful Transmissions by Month (chart):

SELECT
"tx_date",
SUM("valid_runs") as "Transmits"
FROM (
    SELECT date_trunc('month', block_time) as tx_date, COUNT(CASE WHEN success = true THEN 1 END) as valid_runs
    FROM ethereum."transactions"
    WHERE "to" IN (
        SELECT DISTINCT("to") FROM ethereum."transactions"
        WHERE
            "from" = '\xb976d01275b809333e3efd76d1d31fe9264466d0' AND
            substring("data"::bytea FROM 0 FOR 5)::bytea = '\xc9807539'
    ) AND
    substring("data"::bytea FROM 0 FOR 5)::bytea = '\xc9807539' AND
    block_time >= '2020-08-08'
    GROUP BY tx_date
    ORDER BY tx_date DESC
) x
GROUP BY "tx_date"