An address is classed when they are a sender or recipient of a transaction. From a blockchain's perspective, active addresses are important because they generate gas fee revenue. Tracking the number of active addresses gives insight in how effective a blockchain is at keeping users engaged. The following query counts the active addresses on any chain over any date aggregation and range.

SELECT [signed_at:aggregation] as date
       , uniq(addresses) AS active_addresses
			 , chain_name
FROM (
    SELECT signed_at, tx_sender as addresses, chain_name
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
    AND [signed_at:daterange]                     
UNION ALL 
    SELECT signed_at, tx_recipient as addresses, chain_name
    FROM blockchains.all_chains 
    WHERE [chain_name:chainname]
    AND [signed_at:daterange]
) x
GROUP BY date, chain_name
ORDER BY date

Want to learn how we got there? 👇

The following query will retrieve data from the all_chains data table.

SELECT * 
FROM blockchains.all_chains
LIMIT 10
-- This will be quite a large query, so we will need to refine it a bit further!

Finding Unique Sending and Receiving Addresses

A blockchain’s active addresses can be found by counting the number of unique sending and receiving addresses. The all_chains table has two columns that allow you to query sending and receiving addresses - tx_sender and tx_recipient.

Your query needs to contain a subquery that selects tx_sender and tx_recipient addresses separately. If you had a single query that just adds a count() of all unique tx_sender and tx_recipient wallets as such

SELECT count(distinct tx_sender) + count(distinct tx_recipient)

then the addresses that have both sent and received a transaction would be counted twice. The two queries can be joined via UNION ALL.

Adding [chain_name:chainname] and [signed_at:daterange] in the WHERE statement of your query enables you to pivot results by chain and time period. To read more on parameter filters, click here.

-- List of tx_sender addresses
SELECT tx_sender, signed_at
FROM blockchains.all_chains 
WHERE [chain_name:chainname]
AND [signed_at:daterange]

UNION ALL 

-- List of tx_recipient addresses
SELECT tx_recipient, signed_at
FROM blockchains.all_chains 
WHERE [chain_name:chainname]
AND [signed_at:daterange]

Counting Active Addresses

Now that you have a column of addresses you need to transform the query into a sub query - to act as a data table assigned the alias *x.*

SELECT * 
FROM (
SELECT signed_at, tx_sender as addresses
FROM blockchains.all_chains 
WHERE [chain_name:chainname]
AND [signed_at:daterange]

UNION ALL 

SELECT signed_at, tx_recipient as addresses
FROM blockchains.all_chains 
WHERE c[chain_name:chainname]
AND [signed_at:daterange]
) x -- table is assigned alias x

uniq(addresses) AS active_addressesis added to count the number of unique addresses that were active over the select time period. The uniq(addresses)statement is similar to count(distinct addresses) - just shorter and more performant.