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