📂Wallet Examples

Replace from_address, to_address, and the source table with the appropriate values

All Token Movement for a Wallet

SELECT
    token_address,
    from_address,
    to_address,
    value,
    DATE(block_timestamp) AS date
FROM
    `nansen-query.raw_ethereum.token_transfers`
WHERE
    from_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    OR to_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
ORDER BY 
    DATE(block_timestamp) DESC

Token Balance for a Wallet

DECLARE wallet STRING DEFAULT LOWER('0x3637d7f6041d73917017e5d3e2259473215ecf6f');

WITH
txns AS (
    SELECT  
        block_timestamp,
        token_address,
        from_address AS address,
        -CAST(value AS FLOAT64) / 1e18 AS value
    FROM 
        `nansen-query.raw_ethereum.token_transfers`
    UNION ALL
    SELECT
        block_timestamp,
        token_address,
        to_address AS address,
        CAST(value AS FLOAT64) / 1e18 AS value
    FROM
        `nansen-query.raw_ethereum.token_transfers`
)

SELECT 
    token_address,
    SUM(value) AS token_balance
FROM txns 
WHERE address = wallet
GROUP BY
    token_address
HAVING 
    SUM(value) > 1
ORDER BY token_balance DESC

Top 50 Wallet by ETH Balance

SELECT
    SUM(CAST(balance / 1e18 AS FLOAT64)) AS eth_balance,
    address
FROM `nansen-query.raw_ethereum.balances`
GROUP BY address
ORDER BY eth_balance DESC
LIMIT 50

Estimated Location Based On Trading Hours

DECLARE input_address STRING DEFAULT LOWER('0x41339d9825963515e5705df8d3b0ea98105ebb1c');
WITH

all_sent_tx AS (
    SELECT
        block_timestamp,
        from_address,
        transaction_hash
    FROM
        `nansen-query.raw_ethereum.token_transfers`
    WHERE
        from_address = input_address
),

all_sent_tt AS (
    SELECT
        block_timestamp,
        block_hash,
        from_address
    FROM
        `nansen-query.raw_ethereum.token_transfers`
    WHERE
        from_address = input_address
),

all_sent AS (
    SELECT * FROM all_sent_tx
    UNION ALL
    SELECT * FROM all_sent_tt
),

all_hours AS (
    SELECT * FROM UNNEST(GENERATE_ARRAY(0, 23)) AS hour
),

tx_per_hour AS (
    SELECT
      EXTRACT(HOUR FROM block_timestamp) AS hour_of_day,
      COUNT(DISTINCT from_address) AS num_tx_sent
    FROM
        all_sent
    GROUP BY
      hour_of_day
),

list AS (
    SELECT
        hour,
        COALESCE(num_tx_sent, 0) AS txs
    FROM
        all_hours
    LEFT JOIN
        tx_per_hour
        ON
        all_hours.hour = tx_per_hour.hour_of_day
    ORDER BY
        all_hours.hour
),

almost AS (
    SELECT
        MAX(txs) AS most_transactions
    FROM list 
)

SELECT
    most_transactions,
    hour,
    IF(hour < 7, 'APAC', IF(hour > 13, 'USA', 'EURO')) AS continent
FROM almost
LEFT JOIN
    list
    ON most_transactions = txs

Token Flow Path Between Two Wallets

DECLARE start_address STRING DEFAULT LOWER('0x47068105c5feff69e44520b251b9666d4b512a70');
DECLARE end_address STRING DEFAULT LOWER('0x2604afb5a64992e5abbf25865c9d3387ade92bad');

WITH
traces_0 AS (
    SELECT *
    FROM `nansen-query.raw_ethereum.traces`
    WHERE from_address = start_address
),

traces_1_hop AS (
    SELECT
        1 AS hops,
        traces_1.from_address,
        traces_1.to_address,
        traces_1.trace_address,
        traces_1.block_timestamp,
        CONCAT(traces_0.from_address, ' -> ', traces_0.to_address, ' -> ', traces_1.to_address) AS path
    FROM `nansen-query.raw_ethereum.traces` AS traces_1
    INNER JOIN traces_0
    ON traces_0.to_address = traces_1.from_address
    AND traces_0.block_timestamp <= traces_1.block_timestamp 
),

traces_2_hops AS (
    SELECT
        2 AS hops,
        traces_2.from_address,
        traces_2.to_address,
        traces_2.trace_address,
        traces_2.block_timestamp,
        CONCAT(path, ' -> ', traces_2.to_address) AS path
    FROM `nansen-query.raw_ethereum.traces` AS traces_2
    INNER JOIN traces_1_hop
    ON traces_1_hop.to_address = traces_2.from_address
    AND traces_1_hop.block_timestamp <= traces_2.block_timestamp 
),

traces_3_hops AS (
    SELECT
        3 AS hops,
        traces_3.from_address,
        traces_3.to_address,
        traces_3.trace_address,
        traces_2_hops.block_timestamp,
        CONCAT(path, ' -> ', traces_3.to_address) AS path
    FROM `nansen-query.raw_ethereum.traces` AS traces_3
    INNER JOIN traces_2_hops
    ON traces_2_hops.to_address = traces_3.from_address
    AND traces_2_hops.block_timestamp <= traces_3.block_timestamp 
    WHERE traces_3.to_address = end_address
),

traces_all_hops AS (
    SELECT * FROM traces_1_hop
    UNION ALL
    SELECT * FROM traces_2_hops
    UNION ALL
    SELECT * FROM traces_3_hops
)

SELECT *
FROM traces_all_hops
WHERE hops = 3
LIMIT 100