The Question:

Genopets is a move-to-earn game reminiscent of Pokemon Go on Solana.

Recently, Genopets launched staking for their $GENE token read more here .)

Create a visualization showing the amount of staked GENE over time.

• How many unique wallets have staked $GENE since its inception on May 18th?
• What percentage of those stakers have previously purchased a Genopets NFT?
• What trends are you seeing with the Genopets NFT collection?

Preamble

This is a bounty submission for Flipsidecrypto The objective of a bounty submission is to create lasting analyses that appeal to a large general audience with only cursory knowledge of the subject.

I certainly hope that this essay will achieve that goal, but as someone who has the credo "know your audience" branded into my brain, and as someone who just loves to teach what I have learned, I can't help but make my presentaion from the perspective of a bounty hunter.

Once published, this dashboard will be a part of the Flipside growing archive. While not everyone browsing through these dashboards and queries are bounty hunters, they are more than likely interested in not just the 'what', but the 'how', my focus will be on the actual process of how the data is gathered and what my methodology was.

If you are a Genopets fan, I'm sorry, but I just can't get too enthusiastic about a staking token that is illegal for me to use and a market of $4000 creatures. It is an excellent excercise though, and it has given me an opportunity to learn more about NFTs in general and Solana in particular.

LFG!!

First, we query the Flipside Solana labels table. The Flipside team has labeled a lot of stuff! the

Getting this count was not easy! I have not had much experience with these solana tables, but as soon as you overcome the fact that you have to dig into the various columns containing JSON arrays, it becomes much easier! Still tricky as heck, but easier. Here is a really useful link that helped quite a bit Flipside Solana Tutorial

Here's my query:

$GENE address : 'StaKe9nb7aUjXpjpZ45o6uJBsZxj2BWCDBtjk8LCg2v'

Part I: Counting the unique stakers

Solana uses a consensus mechanism called "Proof of History" (POH). Don't ask me to explain this er, I leave the study of this block chain as an excercise for the reader!

What you need to know is that there are lots and lots and lots of transactions. This is not Bitcoin. Bitcoin blocks take an average of 20 minutes or so to complete. Solana? 400 miliseconds. It is supposed to be 766 ms, but you know, there are still a few bugs in the system.

Bitcoin has successfully processed over a trillion dollars in transactions in 13 years time. There has been zero down time. Solana? Well they tend to have to shut down every so often (like today right as I was stressing and trying to use solscan)... but I digress.

My point is that due to the nature of the blockchain, where a transaction is actually a set of instructions (so dont expect a unique result if you query by transaction ID) One transaction is made up of many instructions. Some instructions, additionaly have inner instructions! This means that a lot of tables aren't very tidy.

Slaying the SQL Dragon

The bottom line is that there is a lot of useful information in fields on the table which are not just a single value.

Snowflake has three data types categorized as "semi structured": arrays, objects and variants.

• Arrays are a list of values, enclosed with brackets. You can access each element of the array via an index. If you have a column named 'array_column' that holds an array, say with the values [3,4,5]. array_column[0] would be 3, array_column[1] would be 4 and if you can't figure out array_column[2] you probably shouldn't be bothering with this work.
• Objects are kind of like a dictionary. They are a set of key, value pairs notated like so: {"key1":"value1", "key2":"value2, ...} so if i had a column named "object_column", and I was interested in accessing the value of "key2" I would do 'object_column:key2' which, in our example would yield 'value2'.
• Variants store any combination of arrays, objects and any data type you can dream up. That animal up there in the SQL query above is a variant. That's why there are brackets and colons.

Slaying the Solana Dragon

Part II: Do Stakers Hodl?

Part III: Trendy Genopets

You have to scroll quite a bit, just to see the whole transaction! Our next steps are:

1. Copy and paste the inner instructin into a JSON beautifier so we can see what is going on
2. Go to solscan.io and paste the transaction ID into the search bar and figure out what parts of it we need.

Above are the results. It is a little hard to tell, but play around with it! With the previously mentioned tutorial, and by playing around with the transactions, in no time you too will be a dragon slayer!

Some Dragon Lore

Remember how we found the $GENE staking contract, from the labels table (Solana.dim_labels)? We use that same table to query for the unique token addresses for every token that has a label. I went ahead and queried for all labels containing the string "genopets", (... where label .ilike(%genopet%).

This revealed two different labels — A "genopets" label (a little over 4000) and a "genopets terraform seeds" label, adeing over 40000 more Genopet NFTs to the mix. This got me wondering. I went to Magic Eden , the leading marketplace on Solana and got flummoxed for a while, as there appeard to be several different collections under the one "genopet" umbrella collection.

It turns out that to turn your genopet NFT into a dragon you had to colect several a handful of unique crystals, add a seed and a "habitat was formed". These various items, and a few dragons even were airdropped back in 2021, before Magic Eden was involved.

Before Magic Eden, the only market that supported the Genopet ecosystem was FTX a centralized exchange. Our history begins with a supply of genopets already in existence. We concluded that using those labels on the labels table was a reasonable (and only) approach.

It is always a good idea to make sure you are counting everything at least once and only once!

Getting GeekQL with the SQl

So... to recap:

• We found our token addresses from the labels table
• We sliced into the innards of the events table to get the unique wallets that staked $GENE The next step was to find the actual owners of the genopet tokens. We did this by filtering the solana.fact_transfers table to only include transfers involving our beloved genopets.

That gave us the ability to determine how many of those wallets were in common with those wallets that staked, adding one last filter becasue we were only interested in whether or not the stakers had previously purchased a genopet NFT.

Above we see the total transaction activity since the beginning of 2022 of all genopet NFTs found in the Flipside Solana schema. Below, we see just the activity involving the special crystal genopets.

The Final Query

The final step was much more straightforward and is a standard maneuver in these types of analyses. We had a bunch of transactions. We grouped them by day and counted the transactions for each day. We were asked for trends among the genopets, which no longer involves the staking aspect of our problem or only looking at transactions before May 18.

So with the list of wallets, from combining the labels and transaction table and avoiding the messy innards of the events table, we obtained the results from the two charts above. I went ahead and separated the "expensive" genopets from the seeds to see if that gave us any additional insights.

Got insights?

I could pontificate about the insights gleaned from the above two charts, but the truth is we need more to go on to determine what this data means. We can see very little action involving the "expensive" genopets lately and no huge spike after the staking opened. There is more transaction activity when one views the big picture (dominated by the relatively inexpensive teraform seed genopets) but, like most crypto projects, there is an overall decline.

I am no expert. For me this could mean that everyone is clinging to their beloved dragons or potential dragons, in eager anticipation of the 'Pokemon Go' part of the game, which still hasn't launched, and therefore there are no sales, or interest has simply waned. It is not easy to query prices, but we, perhaps could have looked at the toatl sale prices over time, instead of querying just a total count of transactions.

Inspecting the differences between the various Crystal types (Earth, Metal, Water, Fire, habitat) and delving into the price aspect of the NFTs would be an idea for future research for those (not me) so inclined.

> Then one day it happened. Jackie Paper came no more. And Puff that magic dragon, ceased his fearless roar

• Peter, Paul and Mary (The band not the New Testament characters)

Concluding Thoughts:

We have explored some of the aspects of the Genopet ecosystem. We learned about staking, the different types of Genopets and more importantly, some insights about the Solana ecosytem, the tables available and how to query them. Where we were short on insights, we hope to have made up with some detailed information about how to go about analyzing a query and hauling in the big bount y bucks! Thanks for reading!

• potmo