Growing an engaged user base is top of mind for every project in Web3. Active addresses is a great starting point to track a project's user base growth. The following query counts the active addresses for Crabada - the leading P2E game on Avalanche - over any date aggregation and range. To build this analysis for a different project, insert your project's chain into chain_name and contract address into the tx_recipient. If applicable, add [chain_name]to make your analysis multi-chain.

SELECT  [signed_at:aggregation] as date
        , uniq(tx_sender) AS active_addresses
FROM blockchains.all_chains 
WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
        AND [signed_at:daterange]
        AND chain_name = 'avalanche_mainnet'
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 data table.

SELECT *
FROM blockchains.all_chains 
LIMIT 10
-- Simplest query

Count of Unique Addresses

Transactions interacting with a smart contract often show the contract in the tx_recipient column and the user address as the tx_sender. As you can see in the image below the Crabada: Game contract is always the tx_recipient(To) in every gameplay transaction.

Therefore, to calculate active addresses for Crabada you must include three statements in your query:

• Two SELECT statements:

  1. uniq(tx_sender) counts the unique addresses in the tx_sender column. Read more on uniq() here.

  2. [signed_at:aggregation] enables you to pivot the date aggregation by hour, day, week, month, quarter, year. Read more on [signed_at:aggregation] here.

     SELECT uniq(tx_sender), [signed_at:aggregation]

• One WHERE statement:

  1. Use the following to filter entires where tx_recipient is the Crabada: Game contract. To learn how to use the unhex() function, click here.

     WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')

SELECT  uniq(tx_sender) AS active_addresses
FROM blockchains.all_chains 
WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8') -- Crabada Game Contract
		AND [signed_at:daterange]
		AND chain_name = 'avalanche_mainnet'

Integrating Date Aggregation Filter

Adding [signed_at:aggregation] as date enables you to pivot the date aggregation by day, month , year, etc. To read more on [signed_at:aggregation] and other parameter filters, click here.

SELECT [signed_at:aggregation] as date, uniq(tx_sender) AS active_addresses
FROM blockchains.all_chains 
WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
		AND chain_name = 'avalanche_mainnet'
		AND [signed_at:daterange]
GROUP BY date

Congrats, you just levelled up your SQL skills!