polygon-irenedao-votes.sql

CREATE TEMP FUNCTION
  hex64ToInt64(hex STRING)
  RETURNS INT64 AS (
    IF(hex < "8000000000000000", 
       cast(concat("0x", hex) AS INT64), 
       (SELECT (((ms32 & 0x7fffffff) << 32) | ls32) - 0x7fffffffffffffff - 1
        FROM (SELECT cast(concat("0x", substr(hex, 1, 8)) AS INT64) AS ms32,
                     cast(concat("0x", substr(hex, 9, 8)) AS INT64) AS ls32))));
CREATE TEMP FUNCTION
  KECCAK(data STRING)
  RETURNS STRING
  LANGUAGE js AS """
    return ethers.utils.keccak256(data);
"""
OPTIONS ( library="gs://blockchain-etl-bigquery/ethers.js" );
WITH signatures AS (
  SELECT "observe" as signature, KECCAK(CONCAT("0x",TO_HEX(CAST("observe(address,uint256)" AS BYTES)))) AS keccak
    UNION ALL
  SELECT "voteFor" as signature, KECCAK(CONCAT("0x",TO_HEX(CAST("voteFor(address,uint256)" AS BYTES)))) AS keccak
)
SELECT 
  tx.hash, 
  tx.transaction_index,
  signature, 
  REGEXP_REPLACE(topics[OFFSET(1)], "0x0{24}", "0x") AS from_address, 
  hex64ToInt64(SUBSTR(topics[OFFSET(2)], 3)) AS token_id
FROM 
  `public-data-finance.crypto_polygon.transactions` AS tx,
  `public-data-finance.crypto_polygon.logs` AS logs,
  signatures
WHERE TRUE
  AND tx.block_timestamp > '2022-01-01'
  AND tx.to_address = LOWER('0x2E5a6C9516a9e887390db0a94DEDbb044DF4C409')
  AND logs.transaction_index = tx.transaction_index
  AND logs.transaction_hash = tx.hash
  AND topics[OFFSET(0)] = signatures.keccak
ORDER BY tx.block_number, tx.transaction_index, signature