The following query counts the number of addresses transferring ERC-20 tokens. To build this analysis for a different event, insert the events topic0.

SELECT [signed_at:aggregation] as date,
			 uniq(topic1) AS active_addresses
FROM blockchains.all_chains 
WHERE topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef')
AND [chain_name:chainname]
AND [signed_at:daterange]
GROUP 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!

The goal is to transform this query into one that counts active address transferring tokens on any chain over any period of time. The Transfer event has the exact same structure across all EVM chains.

Token Address = log_emitter

Topic Event Hash = topic0

From/Sender = topic1

To/Recipient = topic2

All Transfer events share the exact same topic event hash (topic0). Therefore, add WHERE topic0 = unhex(’ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef’) to query transactions containing a Transfer event.

SELECT * 
FROM blockchains.all_chains
WHERE topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef') -- Topic Event Hash
LIMIT 10

Including [signed_at:daterange] and [chain_name:chainname] in the WHERE statement enables you to pivot analysis via different date ranges and chain. To read more on [signed_at:daterange], [chain_name:chainname]and other parameter filters, click here.

SELECT * 
FROM blockchains.all_chains
WHERE topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef') -- Topic Event Hash
AND [chain_name:chainname]
ANd [signed_at:daterange]
LIMIT 10

Transfer events display the sender’s address in the topic1 field - as seen below.

To find active addresses, use the uniq(topic1) function to count all unique addresses in the topic1 field.