Tron example queries

This page provides Blockchain Analytics query examples for Tron.

See the BigQuery documentation for instructions on using BigQuery.

Tether transfers with the largest amounts in a month

This query returns the three largest USDT transfers in March 2023.

In the Google Cloud console, go to the BigQuery page.

The following query is loaded into the Editor field:

CREATE TEMP FUNCTION hexToTron(address STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (library=["gs://blockchain-etl-bigquery/ethers.js"])
AS r"""
  function encode58(buffer) {
    const ALPHABET = '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';
    const digits = [0];
    for (let i = 0; i < buffer.length; i++) {
      for (let j = 0; j < digits.length; j++) digits[j] <<= 8;
      digits[0] += buffer[i];
      let carry = 0;
      for (let j = 0; j < digits.length; ++j) {
        digits[j] += carry;
        carry = (digits[j] / 58) | 0;
        digits[j] %= 58;
      }
      while (carry) {
        digits.push(carry % 58);
        carry = (carry / 58) | 0;
      }
    }
    for (let i = 0; buffer[i] === 0 && i < buffer.length - 1; i++) digits.push(0);
    return digits.reverse().map((digit) => ALPHABET[digit]).join("");
  }

  function sha256(msgBytes) {
    const msgHex = ethers.utils.hexlify(msgBytes);
    const hashHex = ethers.utils.sha256(msgHex);
    return ethers.utils.arrayify(hashHex);
  }

  addressBytes = ethers.utils.arrayify('0x' + address.replace(/^0x/, '41'))
  checkSum = sha256(sha256(addressBytes)).slice(0, 4);
  return encode58(new Uint8Array([...addressBytes, ...checkSum]));
""";

WITH transfers AS (
  SELECT
    block_number,
    hexToTron(CONCAT('0x', SUBSTR(topics[1], 27))) AS from_address,
    hexToTron(CONCAT('0x', SUBSTR(topics[2], 27))) AS to_address,
    CAST(data AS INT64) / 1000000 AS amount
  FROM
    `bigquery-public-data.goog_blockchain_tron_mainnet_us.logs` l
  WHERE
    address = '0xa614f803b6fd780986a42c78ec9c7f77e6ded13c' -- USDT contract
    AND ARRAY_LENGTH(topics) = 3
    AND topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer events
    AND DATE(block_timestamp) BETWEEN "2023-03-01" AND "2023-03-31"
)
SELECT * FROM transfers ORDER BY amount DESC LIMIT 3;

The following shows an example result:

Example result
block_number	from_address	to_address	amount
49606052	TV6MuMXfmLbBqPZvBHdwFsDnQeVfnmiuSi	TWd4WrZ9wn84f5x1hZhL4DHvk738ns5jwb	2173544428.073
49076870	TWd4WrZ9wn84f5x1hZhL4DHvk738ns5jwb	TV6MuMXfmLbBqPZvBHdwFsDnQeVfnmiuSi	2005126503.6518
49665517	T9yD14Nj9j7xAB4dbGeiX9h8unkKHxuWwb	TBPxhVAsuzoFnKyXtc1o2UySEydPHgATto	1000000000.0