JimMyersTechNew Accounts Created Per Day
    Updated 2020-11-25
    SELECT
    date_trunc('day', start_date) AS metric_date,
    COUNT(distinct address) AS new_address
    FROM (
    -- Get the first day an address appeared on the to/from side of a tx
    SELECT
    address,
    event_currency,
    min(start_date) AS start_date
    FROM (
    -- Retrieve the first time an address showed up on the 'to' side 
    SELECT
    event_to AS address,
    event_currency,
    min(block_timestamp) AS start_date
    FROM gold.cosmos_events
    WHERE
    event_amount > 0
    AND event_currency = 'ATOM'
    GROUP BY 1,2
    UNION
    -- Retrieve the first time an address showed up on the 'from' side 
    SELECT
    event_from AS address,
    event_currency,
    min(block_timestamp) AS start_date
    FROM gold.cosmos_events tr
    WHERE
    event_amount > 0
    AND event_currency = 'ATOM'
    AND address IS NOT NULL
    GROUP BY 1, 2

    )sq
    Run a query to Download Data