📂
Token Examples
Replace token_address and the source table with the appropriate values
#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, [email protected]
WITH
double_entry_book AS (
-- debits
SELECT to_address AS address, value AS value
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE to_address IS NOT NULL
AND status = 1
AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
UNION ALL
-- credits
SELECT from_address AS address, -value AS value
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE from_address IS NOT NULL
AND status = 1
AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
UNION ALL
-- transaction fees debits
SELECT miner AS address, SUM(CAST(receipt_gas_used AS NUMERIC) * CAST(gas_price AS NUMERIC)) AS value
FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
JOIN `bigquery-public-data.crypto_ethereum.blocks` AS blocks ON blocks.number = transactions.block_number
GROUP BY blocks.miner
UNION ALL
-- transaction fees credits
SELECT from_address AS address, -(CAST(receipt_gas_used AS NUMERIC) * CAST(gas_price AS NUMERIC)) AS value
FROM `bigquery-public-data.crypto_ethereum.transactions`
)
SELECT
address,
SUM(value) AS balance
FROM double_entry_book
GROUP BY address
ORDER BY balance DESC
LIMIT 10
Result should look something like this:
SELECT
token_address,
from_address,
to_address,
value,
DATE(block_timestamp) AS date
FROM
`bigquery-public-data.crypto_ethereum.token_transfers`
WHERE
token_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
ORDER BY
DATE(block_timestamp)DESC
Last modified 14d ago