Smart contracts are typically created through a smart contract creation transaction. Counting the number of smart contract creation transactions is one of the best ways to measure developer activity using on-chain data. Smart contracts that are created from other smart contracts aren’t included in this calculation e.g Pairs aren’t included as they’re created from Factory contracts.

SELECT  [signed_at:aggregation]  as date
        , uniq(tx_creates) 
FROM blockchains.all_chains
WHERE tx_creates != ''
        AND [signed_at:daterange]
        AND [chain_name:chainname]
GROUP BY date

Want to learn how we got there? 👇

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

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

Counting Contract Creation Transactions

The goal is to transform this query into one that counts contract creation (CC) transactions on any given chain over any given time period. CC transactions can be calculated by counting entries in the tx_creates column of the all_chains table. To count these contracts, you need to insert two statements:

SELECT statement
- uniq(tx_creates) to count the number of uniq CC transactions. Learn more about uniq() and other aggregate functions here.
WHERE statement
- tx_creates != '' to filter out all the rows in the tx_creates column that don’t contain a contract - i.e no contract was created. If you don’t specify this in the WHERE statement, your query will just count all the transactions.

Also, 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.

-- Total Contract Creation Transactions on Avalanche
  SELECT    
		uniq(tx_creates) as tx_count                                                        
    FROM blockchains.all_chains
		WHERE tx_creates != ''
		AND [signed_at:daterange]
		AND [chain_name:chainname]

As is, the query above will give you a single cell with the count of every transactions that has happened on Avalanche over your select date range 👇

Integrating Chain and Date filters

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] click here.

-- Daily CC Transactions for Avalanche
SELECT  [signed_at:aggregation]  as date
        , uniq(tx_creates) 
FROM blockchains.all_chains
WHERE tx_creates != ''
        AND [signed_at:daterange]
        AND [chain_name:chainname]
GROUP BY date

Congrats! You just added a new query to your arsena