Rodolfo-LimaNew Addresses Accumulated - Optimism
    Updated 2022-08-09
    -- DAILY UNIQUE ADDRESSES on Optimism, Ethereum and Polygon
    WITH OPT_DUA AS( -- OPTIMISM
    WITH FROM_ADDRESSES AS (
    SELECT DISTINCT
    MIN(DATE_TRUNC('day',BLOCK_TIMESTAMP)) AS DATE_MIN,
    FROM_ADDRESS AS UNIQUE_ADDRESS
    FROM
    optimism.core.fact_transactions
    WHERE DATE_TRUNC('day',BLOCK_TIMESTAMP)>= '2022-07-01' AND DATE_TRUNC('day',BLOCK_TIMESTAMP) <= '2022-07-31'
    GROUP BY 2
    ORDER BY 1 ASC
    ),
    COUNT_UA AS (
    SELECT
    DATE_MIN,
    COUNT(DISTINCT(UNIQUE_ADDRESS)) AS DAILY_UA
    FROM
    FROM_ADDRESSES
    GROUP BY DATE_MIN
    ORDER BY DATE_MIN)

    SELECT
    SUM(DAILY_UA) AS OPT_TOTAL_UA
    FROM
    COUNT_UA
    ORDER BY DATE_MIN
    ),

    POL_DUA AS (-- POLYGON
    WITH FROM_ADDRESSES AS (
    SELECT DISTINCT
    MIN(DATE_TRUNC('day',BLOCK_TIMESTAMP)) AS DATE_MIN,
    FROM_ADDRESS AS UNIQUE_ADDRESS