Updated 2024-12-12
    WITH recent_transactions AS (
    SELECT
    from_address AS address,
    MIN(block_timestamp) OVER (PARTITION BY from_address) AS first_tx_date,
    block_timestamp
    FROM BLAST.core.fact_transactions
    WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())
    UNION ALL
    SELECT
    to_address AS address,
    MIN(block_timestamp) OVER (PARTITION BY to_address) AS first_tx_date,
    block_timestamp
    FROM BLAST.core.fact_transactions
    WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())
    ),


    user_metrics AS (
    SELECT
    COUNT(DISTINCT CASE WHEN first_tx_date >= DATEADD(day, -30, CURRENT_DATE()) THEN address END) AS new_users,
    COUNT(DISTINCT address) AS active_users
    FROM recent_transactions
    ),
    total_users AS (
    SELECT
    COUNT(DISTINCT from_address) + COUNT(DISTINCT to_address) AS total_users
    FROM BLAST.core.fact_transactions
    )

    -- Final aggregation
    SELECT
    u.new_users,
    u.active_users,
    t.total_users
    Last run: 2 months ago
    NEW_USERS
    ACTIVE_USERS
    TOTAL_USERS
    1
    1981841981843346485
    1
    25B
    11s