Analyze The Space on-chain data from Dune Analysis

It's been a long time since I posted on Matters. When I saw the launch of TheSpace.Game this time, I suddenly remembered that I could do some on-chain data analysis for it.

The origin is different from the single-contract NFT projects such as Travelloggers / Logbook previously released by Matters. TheSpace is the first multi-contract large-scale project issued by Matters, and the project website has given the contract addresses used by it https://wiki. Each address in thespace.game/addresses can be directly viewed by linking to Polygonscan. This is convenient, but some long-term indicators should be counted, such as asking "How many people have participated in this pixel game since its launch?" Go up one by one address? Obviously not reliable and not scalable

In the past one or two years, as many on-chain projects have become more and more complex, their functions have become more complete, or they have become more and more refined, it is inevitable that the amount of contract code will increase, which has exceeded Ethereum in many cases. The limit that can be carried by a single block, at this time, if you continue to use a single contract, it cannot be successfully deployed at all. The contract must be divided into functional modules and separated as sub-contract subordinates, and it also brings additional benefits such as contract upgrades. TheSpace's multi-contract subordinates also appear to be on the path to refined contracts.
From the perspective of a single contract, Polygon is already complicated enough, and the multi-contract address further increases the difficulty of statistical work.

Try out the Dune.com Analysis tool

Fortunately, in the past year or two, the tools at all levels of Blockchain have become more and more perfect, including on-chain analysis at the Infrastructure level. As long as the demand you can imagine is large enough, someone will definitely try to solve it.
In the field of analysis, what is the most convenient and flexible way to do statistical analysis? Of course, the first push SQL

One of the startups called Dune Analysis focuses on the analysis needs of this on-chain data, and was born in 2020 in Oslo, the capital of Norway. And since its establishment three years ago, it has been able to get a lot of financing from the capital market every year, especially in 2022/February, the most recent one was a Series B financing of US$69 million (a total of nearly 80 million), which is particularly dazzling and its valuation is also direct. Advance to the Unicorn Club. If you are interested, you can go directly to Crunchbase to check it out. From the blog , it seems that at least in 2022/March, the number of companies will remain at only 16!

Oslo, Oslo, Norway; 11-50; Series B total funded amount $79.4M;
latest round on Feb 2, 2022 ; Series B - Dune Analytics ; 3 ; $69.4M ; Coatue;

The current status of Dune platform's support for each Blockchain

1. Dune started by importing Postgresql into the Ethereum Mainnet chain, and then gradually expanded the data of other chains such as Poygon, Binance Chain, Sonala, etc.
2. Postgresql is a good tool for affordable databases, both open source and free to use. It is also a general-purpose relational database, OLTP (Online Transactional Processing), which means that its design balances query (SELECT) and change (INSERT, UPDATE, DELETE ) various business needs; but when the amount of data to be searched reaches the massive level, which means that the daily new increment exceeds millions or reaches the order of billions, Postgresql will become slow.
3. Dune first released V2 Engine since 2022. The bottom layer is SparkSQL based on the Apache Spark project of Databricks. This is an analytical database OLAP (Online Analytical Processing), which is characterized by optimization of analytical tasks (SELECT). It is said that V2 Engine improves 10 times more efficient!
   The first application of V2 Engine is the Sonala chain, and then the data of the Ethereum chain is gradually imported. There is no Polygon chain yet.
   
4. For the Ethereum/Polygon chain, the first thing Dune does is import the original transaction table (ethereum.* polygon.*) because the input data that can be queried in the original transaction table polygon.transactions are all contract-specific data, so polygon.transactions does not It is not convenient to query contract-specific information.
   So in addition to the original transaction table, Dune has added several layers of abstraction to it. The second level of the abstraction layer is that the project party of each contract can submit the contract address to Dune. After Dune analyzes the function on the contract and approves it (it takes a few days), the decoded tables will be generated.
5. One level up in the decoded tables, Dune can also classify and re-merge contracts with similar purposes, and merge into a single dex.trades Tables such as Uniswap, Sushiswap, and other DEX exchanges can be merged into one place, Further improve analysis efficiency
6. There are other off-chain data that are also imported by Dune: For example, the price comparison of ETH/USD, which is formed based on the supply and demand in each centralized exchange, the price comparison itself does not come from the chain, but the value analysis of the chain ecology Crucially, Dune also included them all.
7. The last point is how much Delay is there for each data? The technical essence of Dune is Data Engineering. As long as the terms Data Engineering / ETL / Data Pipeline are mentioned, what cannot be avoided is how much Delay is there from Real-time? The biggest difficulty in data engineering is that real Real-time is impossible. Reality is up to 3 minutes delay, or 3 hours delay, or 3 days delay? The closer to the Real-time, the greater the engineering cost. The answer given in the current Dune Docs is up to 3 hours delay. However, it can be seen from the establishment process of the above abstract layer data table that the higher the upper layer, the more delay will be. Therefore, when doing query analysis, if the original table of the upper layer is used, the smaller the delay, the more likely it is to be close to Real-time.

Dune Analysis Implementation

1) Chart1: How many people have participated in TheSpace, how many accounts, how many transactions?

This question does not involve the in-contract information of TheSpace, which can be directly queried from polygon.transanctions

To use Dune, you first need to apply for a Dune account, which can be created by using your email/password, which will not be described here.
After logging in with the Dune account, you can click New Query to create a new query, select ( 3. Polygon ) in the left column, and then enter polygon in the bottom column. Query which data tables are available, and which columns are available for each table. .
Here you can see the most critical items: from is the source account, to is the destination account, as well as block number, transaction time, etc. If the first step is to check the number of transactions, the corresponding SQL is COUNT(...), for example

 SELECT COUNT(*) FROM polygon.transactions

Can give the number of all transactions on all polygon chains since its birth

To inquire about the TheSpace protocol, all you need is to filter the "to" account, such as

 SELECT COUNT(*)
FROM polygon.transactions
WHERE "to" = '\x264808855b0a6a5a318d238c6ee9f299179f27fc'

Then give the number of records related to all TheSpace Token

Going a step further, if you want to find out how many unique "from" accounts there are, the corresponding SQL is COUNT(DISTINCT ...)

The corresponding entire query is:

 SELECT COUNT(DISTINCT "from")
FROM polygon."transactions"
WHERE "to" = '\x264808855b0a6a5a318d238c6ee9f299179f27fc'

The conclusion is 669.

2) Chart2: How many people have received $Space coins? How many $Space coins did you receive? ( from the initial airdrop )

The 669 accounts found in the previous step actually got $Space coins for various reasons and directly interacted with the TheSpace Token contract, including those who received $Space coins by transfer, and those who only traded in DEX. To further query who got it from airdrop, you can query it from decoded tables. I have submitted several contract addresses for TheSpace and Dune has generated thespace.* This contains a lot of TheSpace level event tables. Therefore, more comprehensive data on receiving coins can be found from the event table:

 SELECT COUNT(*)
FROM thespace."MerkleDistributor_evt_Claimed"

The answer was 1,349. So it seems that although the project team mentioned that airdrops were prepared for more than 5,000 accounts, only 1,349 actually received coins.

In this step, you can also do Visual Board by the way, that is, click "New visualization", there are line graphs, bar graphs, Pie graphs, etc. In this step, there is only count analysis, so choose Counter, which is presented as so

3) Chart3: How many people draw on TheSpace every day? How many pixels have been drawn?

Let’s talk about the answer first. The SQL used in https://dune.com/queries/890566/1556569 is still a bit long, so I won’t copy everything here. Those who are interested can go and see it by themselves (hint: use thespace."TheSpaceRegistry_evt_Color" event table )

Here only the Visual Chart built by Share, you can see that as of 6/9 (UTC time), there are 168+5 accounts in the drawing, if you click on the Query results tab, you can also see the specific daily figures , and the cumulative number of pixels drawn every day. As of now (6/9 UTC), 18,933 pixels have been drawn, including those that have been competed. It seems that it is still a little far from the full picture of 1 million pixels. less than 2%

Summary, and Dune's Share Board

Each of the above Visual Charts also stays in a single Question stage, the Charts of each Question can be shared individually, and a Board can be created to contain multiple Charts for more centralized display.

https://dune.com/bluebasketbooks/thespace-dashboard

Here is TheSpace Board I'm building, which is still being updated. In order to avoid the single article being too long, it will be continued from the next article. You can also ask questions, what other TheSpace data do you want to know?