Counting a blockchain's new addresses is a great insight into how effective they are at onboarding users. The following query counts the new addresses over a given date aggregation and range. Put simply, the query finds the first time each address transacted and counts how many other addresses transacted for the first time over a specific date aggregation (day, month, year, etc).

WITH active_addresses AS (
    SELECT signed_at, tx_sender as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]                     
UNION ALL 
    SELECT signed_at, tx_recipient as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
)
SELECT date, uniq(address) 
FROM (
    SELECT min([signed_at:aggregation]) AS date, address
    FROM active_addresses 
    GROUP BY address
) oc     
WHERE [date:daterange]
GROUP BY date
ORDER BY date desc

Want to learn how we got there? 👇

The following query will retrieve all the entries in the all_chains table.

SELECT * 
FROM blockchain.all_chains
-- The simplest query

Finding Active Addresses

You are going to transform this query to count the daily new addresses. New addresses can be found by query the first time an address transacted on the network. Therefore, you must start with finding active addresses. To learn how to built the query below, view the tutorial here.

WITH active_addresses AS (
    SELECT signed_at, tx_sender as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]                     
UNION ALL 
    SELECT signed_at, tx_recipient as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
)

Finding The First Time The Address Transacted

From the following CTE, you need to find the first day an address was active (transacted) on the network. To do so, add *min([signed_at:aggregation])* and address to the SELECT statement. Click here to read more on min() and here to read more on the [signed_at:aggregation] parameter filter.

GROUP BY address needs to be added to apply the aggregate function min([signed_at:aggregation]) to each unique address.

WITH active_addresses AS (
    SELECT signed_at, tx_sender as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]                     
UNION ALL 
    SELECT signed_at, tx_recipient as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
)
SELECT min([signed_at:aggregation]) AS date, address
    FROM active_addresses 
    GROUP BY address

Counting Daily New Addresses

Now you have a list of addresses and the first time they transacted on the network. In order to then count these addresses on a given day you need to turn the final SELECT statement into a subquery.

WITH active_addresses AS (
    SELECT signed_at, tx_sender as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]                     
UNION ALL 
    SELECT signed_at, tx_recipient as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
)
SELECT * 
FROM(
    SELECT min([signed_at:aggregation]) AS date, address
    FROM active_addresses 
    GROUP BY address 
)

Using this subquery, you can include the following SELECT statements to count unique addresses within a certain date aggregation:

1. date - the minimum date assigned to each address
2. uniq(address) - to read more on the uniq() function, click here.

WITH active_addresses AS (
    SELECT signed_at, tx_sender as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]                     
UNION ALL 
    SELECT signed_at, tx_recipient as address
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
)
SELECT date, uniq(address) 
FROM (
    SELECT min([signed_at:aggregation]) AS date, address
    FROM active_addresses 
    GROUP BY address
) oc     
WHERE [date:daterange]
GROUP BY date
ORDER BY date desc

Congrats! You just added a new query to your arsenal.