📂
Project Examples
Replace token and dataset with the appropriate address and dataset
DECLARE token STRING DEFAULT LOWER('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48');
WITH
double_entry_book AS (
-- debits
SELECT
to_address AS address,
CAST(value AS FLOAT64) AS value,
DATE(block_timestamp) AS date
FROM
`bigquery-public-data.crypto_ethereum.token_transfers`
WHERE
token_address = token
UNION ALL
-- credits
SELECT
from_address AS address,
-CAST(value AS FLOAT64) AS value,
DATE(block_timestamp) AS date
FROM
`bigquery-public-data.crypto_ethereum.token_transfers`
WHERE
token_address = token
),
double_entry_book_grouped_by_date AS (
SELECT
address,
date,
SUM(value) AS balance_increment
FROM
double_entry_book
GROUP BY
address, date
),
daily_balances_with_gaps AS (
SELECT
address,
date,
SUM(balance_increment) OVER (PARTITION BY address ORDER BY date) AS balance,
LEAD(date, 1, DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)) OVER (PARTITION BY address ORDER BY date) AS next_date
FROM
double_entry_book_grouped_by_date
),
calendar AS (
SELECT
date
FROM
UNNEST(GENERATE_DATE_ARRAY(DATE(2015, 8, 7), CURRENT_DATE())) AS date
),
daily_balances AS (
SELECT
address,
calendar.date,
balance
FROM
daily_balances_with_gaps
INNER JOIN
calendar
ON daily_balances_with_gaps.date <= calendar.date
AND calendar.date < daily_balances_with_gaps.next_date
)
SELECT
date,
COUNT(DISTINCT address) AS unique_addresses
FROM
daily_balances
WHERE
balance > 0.0000000001
GROUP BY
date
ORDER BY
date DESC
Last modified 14d ago