📂
Wallet Examples
Replace from_address, to_address, and the source table with the appropriate values
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
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
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
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
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
Last modified 6mo ago