The second on-chain analysis of The Space: Hall of Fame drawing hero list, land occupation ranking list, and land price list

Last week, I saw " The Space | Crazy Night, One Thousand Pixels Drawn " and I've always wondered, how many people on the current map besides @Daisy?

Continuing the use of thespace."TheSpaceRegistry_evt_Color" events table, because all drawing events (events) must emit a color event, then query the evt_Color table to get everyone's drawing process,

 SELECT owner, COUNT(*) AS count_drawings,
COUNT(DISTINCT "tokenId") AS count_pixels
FROM thespace."TheSpaceRegistry_evt_Color"
GROUP BY 1
ORDER BY count_drawings DESC

As mentioned in the previous article, COUNT(*) can do overall statistics. In addition, it can also classify statistics, which is the GROUP BY statement. After SELECT, put the items you want to classify, use owner here, and in FROM .. Add GROUP BY after .table ... Then after SELECT, you can use COUNT as the count of "classified by this owner", for example, COUNT(*) counts the entire number of drawings, and you can also use COUNT(DISTINCT "tokenId") to count the difference. The number of repeated tokenIds, where the tokenId corresponds to the number of one million pixels, which is also commonly understood as the number of the plot; click "Run" to query, you can see that 203 wallets have been drawn so far; It can be seen that count_drawings vs count_pixels is different. There should be a lot of competition for pixels. They were painted by others, and they were taken back by themselves. Of course, it may be that many people are not satisfied with the painted colors. Paint again with another color.

 SELECT owner, COUNT(*) AS count_drawings,
COUNT(DISTINCT "tokenId") AS count_pixels,
SUM(gas_used*gas_price)/1e18 AS gas_matic
FROM thespace."TheSpaceRegistry_evt_Color"
JOIN polygon.transactions ON hash=evt_tx_hash
GROUP BY 1
ORDER BY count_drawings DESC

In addition, because the event table has the original evt_tx_hash corresponding to the transaction on the chain, its use can also be shown here by the way, that is, JOIN Query, joint query on the raw table such as polygon.transactions, the joint field is evt_tx_hash Corresponding to the hash on polygon.transactions It can be imagined that two tables are docked together according to the same hash for convenience. The example here only uses gas_used*gas_price in polygon.transactions, which is the actual matic gas fee. In the GROUP BY statement, in addition to COUNT, there is a similar SUM(...), as the name suggests, it is a statistical summation, see How much Gas did they spend in TheSpace, because Gas is used in Matic, and it has 18 decimal places inside the same as Ether, so dividing it by scientific notation (1e18) is to use Matic as the unit

The query results have been added to this Dashboard https://dune.com/bluebasketbooks/thespace-dashboard

Among them, Daisy's account \x2af159c310... is only ranked second, so who is the number one \x64110f5b... on the list?

Or, because the above result is the total of the ten days since TheSpace was launched, if it is calculated on a single day, will Daisy's painting of 1,000 pixels in one night be the first? The answer is here, https://dune.com/queries/920536?d=7 Hint: Classification by ㄖ is just adding a dimension of GROUP BY, the syntax in PostgreSQL is date_trunc('day', evt_block_time) ::date AS ... Converted to a date, the result is still \x64110f5b... Ranked at the top of the list with 1,117 drawings per day~ (Of course, the UTC international date is commonly used here, and the single-day drawing of your actual residence may be a lot Before and after UTC is divided into two days, those who are interested can also check the time zone function of PostgreSQL to convert to use the local period)

(2) Ranking list of land occupation

The above question has been added to Dashboard a few days ago, and has been discovered by sharp-eyed readers ( @naozi) , how can it be different from Polygonscan? There may indeed be a difference, because the last article said that Dune's data may have an up to 3 hours delay. But if three hours have passed, and there are no new drawing events on TheSpace for the past three hours, then there must be one side of the data that has not been updated to the latest.

Another reason, from the possible statistics of different aspects of things.

Sorry for this Quantity(token) quantity on Poygonscan,

Specifically, drawing events are not the same concept as Planck (PLK) holders. The drawing event is just drawing, it may be painting your own land, but changing the color without transfer of ownership; while holding a Planck (PLK) may simply buy the land without changing the color.

So, I researched again on Polygonscan, if I only bought land without repainting the color, what kind of events would there be? The answer is found in thespace."TheSpaceRegistry_evt_Transfer", this events table records the simple ownership transfer, with "from " "to" "tokenId" is enough. Of course, similar to other events table, there are also evt_block_time/evt_hash/.. etc., which can be used for JOIN Query when needed

 WITH last_owners AS (
  SELECT DISTINCT ON ("tokenId") *
  FROM thespace."TheSpaceRegistry_evt_Transfer"
  ORDER BY "tokenId", evt_block_number DESC, evt_index DESC
)

SELECT "to", COUNT(*),
  MAX(evt_block_time) AS latest,
  (ARRAY_AGG("tokenId" ORDER BY evt_block_time DESC))[1:5] AS last_5
FROM last_owners
GROUP BY 1
ORDER BY count DESC, latest DESC

The same is said here first, you need to check the result from thespace."TheSpaceRegistry_evt_Transfer", but because this event table records all ownership transfers, and in order to check only the last owner (landlord) for each Planck (PLK) , a DISTINCT ON ("tokenId") query is required, which means that for each Planck transaction, you only need to know the last record of changing hands. After ORDER BY "tokenId", you can query the last record sorted by Descending by time. You can use evt_block_time DESC and evt_block_number DESC, which are almost the same, but in other similar high-frequency trading situations where multiple tokenIds are in the same block_number, the same evt_block_time is obtained, so evt_index DESC is added to provide an additional layer of protection.

Get \x2af159c310e... which corresponds exactly to the Polygonscan result with 2841 holdings! 197 accounts hold 27,000+ corresponding to more than 27,000 developed land plots, and the reason it is not consistent with the previous 203 accounts is that someone has drawn but later all the land plots in their hands have been bought by others

Here, MAX(evt_block_time) is also used to get the last starting time of each owner, and (ARRAY_AGG(...))[1:5], etc., by the way, to get the last 5 blocks of each owner (Planck) number for easier comparison.

(3) Land Price List

The land price list is also the most asked. The same working principle is also found on Polygonscan. What events will be triggered during the pricing process? Then look for these decoded tables in thespace.*

The answer is found in thespace."TheSpaceRegistry_evt_Price"

 WITH prices AS (
  SELECT DISTINCT ON ("tokenId") price/1e18 AS price_spaces, *
  FROM thespace."TheSpaceRegistry_evt_Price"
  ORDER BY "tokenId", evt_block_time DESC
)
SELECT *
FROM prices
-- WHERE price_spaces>=100
ORDER BY price_spaces DESC, evt_block_time DESC

In the same principle, DISTINCT ON ("tokenId") is also used to only care about checking the last price of each Planck, de-duplication, and get a temporary prices table. At the same time, because $Space coin also uses 18 decimal places internally, in order to read the price For convenience, first calculate price/1e18 AS price_spaces to get the number in $Space

Finally, read all columns from SELECT * FROM prices, and want to know the pricing list? Just sort by price_spaces in descending order to get the price list of all 27,000+ parcels that have been developed (minted),

Where "-- WHERE price_spaces>=100" starts with -- is commented out in SQL, it means not running for the time being, you can also try to uncomment to see how many plots are priced more than 100$Space coins? Wondering what the most expensive land prices are? The answer is 1,0000,000 $Space, 100 million $Space coins! !

 price_spaces tokenId price owner
100000000 728415 1e+26 \x437b672246e4e2....
100000000 683092 1e+26 \x437b672246e4e2....

Where is the specific location, let's have a look? Calculate inversely according to tokenId 728415 = (y-1)*1000+x, which is located at (415,729), here~

What does the red "Default Risk" mean? Where should I call "tax collector"? This guy has due tax more than 34 million