Active Addresses For A Project is a useful overview; however, it should be used as context to guide deeper analysis. For example, the following query breaks active addresses out by new and existing. If new addresses consistently make up a majority of your active addresses, then you may have significant user churn. On the flip side, if a majority of active addresses are ones that have already used your products (existing) then you might have a problem onboarding new users. These insights will help guide whether capital should be allocated to building a better product or a more optimised onboarding strategy.

with user_cohorts as (
    SELECT  tx_sender as address
            , min([signed_at:aggregation]) as cohortDate
    FROM blockchains.all_chains
    WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
AND  chain_name = 'avalanche_mainnet'
    GROUP BY address
),
     new_users as (
    SELECT  cohortDate as date, uniq(address) as new_users_count
    FROM user_cohorts uc
    GROUP BY date
),
     all_users as (
    SELECT [signed_at:aggregation] as date
        ,uniq(tx_sender) as total_players
    FROM blockchains.all_chains
    WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
AND  chain_name = 'avalanche_mainnet'
 GROUP BY date
)
    SELECT  au.date
         , nu.new_users_count
         , au.total_players - nu.new_users_count AS Existing_Users
         , (nu.new_users_count/au.total_players)*100 as New_User_Percentage
    FROM all_users au
    LEFT JOIN new_users nu
        ON au.date = nu.date;

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 
-- Simplest query

User Cohorts

The goal is to transform the simple query above into one that compares new and existing users for Crabada. The final query uses 3 CTEs:

• user_cohorts
• new_users
• all_users

Lets start by building user_cohorts. The goal of building this CTE is to get a list of addresses and the first month that they transacted - as seen in the image below.

The image above shows the transactions that are happening by the Crabada: Game (gameplay) contract. As you can see every tx_sender of a transaction is different but the tx_recipient is always the Crabada: Game contract. Therefore, you need to query the addresses in the tx_sender column where the tx_recipient is the Crabada: Game contract.

The problem is,

• The Crabada: Game contract looks like this:
  • 82a85407BD612f52577909F4A58bfC6873f14DA8
• Entries in the tx_recipient column look like this:
  • 4�f�P��w�~Fq.

To transform the Crabada contract into the weird looking symbols (a.k.a byte representation) seen in the tx_recipient column, use unhex(). Read more on the unhex() function here.

WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')

SELECT  tx_sender
    FROM blockchains.all_chains
    WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
    AND chain_name = 'avalanche_mainnet'