check_skedArbitrum Historical Unique Addresses
    Updated 2023-04-12
    WITH all_addresses AS (
    SELECT sender_address AS address, _day
    FROM (
    SELECT FROM_ADDRESS AS sender_address, BLOCK_TIMESTAMP AS _day
    FROM arbitrum.core.fact_token_transfers
    UNION ALL
    SELECT ETH_FROM_ADDRESS AS sender_address, BLOCK_TIMESTAMP AS _day
    FROM arbitrum.core.ez_eth_transfers
    )
    WHERE sender_address IS NOT NULL

    UNION

    SELECT receiver_address AS address, _day
    FROM (
    SELECT TO_ADDRESS AS receiver_address, BLOCK_TIMESTAMP AS _day
    FROM arbitrum.core.fact_token_transfers
    UNION ALL
    SELECT ETH_TO_ADDRESS AS receiver_address, BLOCK_TIMESTAMP AS _day
    FROM arbitrum.core.ez_eth_transfers
    )
    WHERE receiver_address IS NOT NULL
    ),

    distinct_addresses AS (
    SELECT DISTINCT address, _day
    FROM all_addresses
    )

    SELECT DATE_TRUNC('day', _day) AS _day, COUNT(DISTINCT address) AS "Unique Addresses_ARB"
    FROM distinct_addresses
    GROUP BY DATE_TRUNC('day', _day)
    ORDER BY DATE_TRUNC('day', _day)