📂Project Examples

Replace token and dataset with the appropriate address and dataset

Unique Addresses for Project

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