📂Token Examples

Replace token_address and the source table with the appropriate values

Top 10 ETH Balance By Wallet

#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, [e-mail]
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:

Source: https://medium.com/google-cloud/how-to-query-balances-for-all-ethereum-addresses-in-bigquery-fb594e4034a7

All Historical Trades for a Token

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