The following query counts the daily transactions for the Start Game event on Crabada - the leading P2E game on Avalanche. To find transactions for a different event, change the topic0 to the new event hash and, if applicable, the chain_name. Making this analysis multi-chain is a matter of adding [chain_name:chainname] to the WHERE statement.
SELECT
[signed_at:aggregation] as date,
uniq(tx_hash) AS tx_count
FROM blockchains.all_chains
WHERE successful = 1
AND chain_name = 'avalanche_mainnet'
AND topic0 = unhex('0eef6f7452b7d2ee11184579c086fb47626e796a83df2b2e16254df60ab761eb')
GROUP BY date
The following query will retrieve all the log events.
SELECT * FROM blockchains.all_chains
-- This will be quite a large query, so we will need to refine it a bit further!
The goal of the following tutorial is to count the number of Start Game events on Crabada. Start Game is an event that initiates a miners quest to find loot, giving unique insight into how many miners are playing the game.
Each transaction has a unique transaction hash which can be found in the tx_hash column. Due to the all_chains table having all the block, transaction and log event data in one table it prints the same transaction hash (tx_hash) for each log event.
To count all unique transaction hashes, insert uniq(tx_hash) into the SELECT statement. uniq(tx_hash) is equivalent to count(distinct tx_hash) - just shorter 😉.
Increment's database also holds the tx_hash of unsuccessful transactions - add the following to the WHERE statement to filter out every unsuccessful transaction.
WHERE successful = 1
Whenever you want to query results from Avalanche you should use chain_name = 'avalanche_mainnet’ to help optimise your query.
-- Total Transactions for Avalanche
SELECT uniq(tx_hash) as tx_count
FROM blockchains.all_chains
WHERE successful = 1
AND chain_name = 'avalanche_mainnet'
As is, the query above will give you a single cell with the count of every transactions that has happened on avalanche - as you can see below 👇
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.
-- Transactions for Avalanche
SELECT
[signed_at:aggregation] as date,
uniq(tx_hash) as tx_count
FROM blockchains.all_chains
WHERE successful = 1
AND chain_name = 'avalanche_mainnet'
GROUP BY date