Mirror, Writing & Subscribing NFTs - Weekly Collectors

Mirror, Writing & Subscriber NFTs

Trying to find the most common calls on a contract

WITH topic_by_last_occurence AS (
  SELECT
    SUBSTRING(TO_HEX("data"), 1, 8) AS funcsig,
    "hash",
    ROW_NUMBER() OVER (PARTITION BY SUBSTRING(TO_HEX("data"), 1, 8) ORDER BY "block_time" DESC) AS rn,
    COUNT(*) OVER (PARTITION BY SUBSTRING(TO_HEX("data"), 1, 8) ORDER BY "block_time") AS "total_calls_of_function (by a signer)"
  FROM ethereum."transactions"
  WHERE
    "to" = FROM_HEX('3f2408693cc2e0c8e0bb68f039ceb6deac0ec072') AND NOT "data" IS NULL
)
SELECT
  CONCAT(
    CAST(COALESCE(CAST(COALESCE(TRY_CAST(0x AS VARCHAR), '') AS VARCHAR), '') AS VARCHAR),
    CAST(COALESCE(CAST(COALESCE(TRY_CAST(funcsig AS VARCHAR), '') AS VARCHAR), '') AS VARCHAR)
  ) AS "funcsig (function method called on etherscan)",
  "total_calls_of_function (by a signer)",
REPLACE(TO_HEX("hash"), 'x', '0x') AS last_tx_with_function_call
FROM topic_by_last_occurence
WHERE
  rn = 1
ORDER BY
  "total_calls_of_function (by a signer)" DESC