Web3 SQL Weekly #6: Finding Metaverse Landlords by creating and using labels
Creating a label for Sandbox, Decentraland, Cryptovoxels, and Otherdeed land traders so it's easy to identify which wallets are the most active "landlords".
Welcome back! I’ll be breaking down one (community submitted) query a week into byte-sized bits, making both the SQL and Blockchain concepts more digestible.
You can find all the weeks here, and submit/vote on future week queries here.
If you’re a complete beginner, you can reference this guide for any terms that are unfamiliar.
🏆 This weekend (3/5/23) we have a labels hackathon where if you create a query to label wallets then you could earn 500 USDC + a Dune Beanie! All details can be found here, with tons of helpful queries and dashboards for you to start from.
This week’s query will be an example you can draw inspiration from!
The video goes much more in the depth on explanations and examples, but you can also just read the written overview below:
What:
We recently revamped Dune labels tables to be much easier to use and contribute to. The tweet below contains some labels you can find in the labels.all table:
I’m going to walk through creating a Metaverse Landlord label, by finding those who have bought at least 100 tokens of land across Sandbox, Decentraland, Cryptovoxels, and Otherdeed.
We’re then going to use that label in a simple query, the same way you would use it in the labels.all table.
Blockchain concepts:
This week I’m going to spend this section talking about how I navigate other people’s queries and dashboards.
I know that for each metaverse project, there is some sort of token representing land. I also know that it’s probably an NFT. So I search in the Dune dashboard page for any dashes containing these projects, starting with “sandbox”. You’ll see that 21shares has actually already done a dashboard with three of the projects:
There’s one query in here that shows trading by land, which if I click into, I can then find all of the times that nft.trades is referenced and pull relevant collection addresses from there.
I know the field I’m looking for is nft_contract_address so I can pull this one for mana, which is related to Decentraland. I can also check etherscan and see that this is the right contract and is indeed an erc721 token. The Otherdeed land address can be found here. The Sandbox land address can be found here.
But Cryptovoxels is missing! So let’s see what we can find on this. I can quickly find a dashboard that shows old parcel stats, and clicking into the “Total # of Parcels” query shows me that a cryptovoxels."Parcel_call_mint" table is called. I will open the query explorer and search for this table to get the contract address quickly. Then I hover over the eye to get a table preview:
The contract_address here is the contract being called, so that must be our parcel address. Parcels though are actually an erc20 token! I believe that the number of tokens you transfer represents the token id - a bit nontraditional, but it has been adopted by marketplaces now (you can check this by looking at a trade).
So this means we should be able to query nft.trades with all four of these addresses - let’s get into the query.
SQL Query Walkthrough:
Here’s the query we’re going to create today.
We already have the four addresses, so we must create a query with nft.trades and filter for buyers across those four contract addresses. You’ll see in the video I started by trying to find buyers who have bought at least 3 tokens from each project - but there were only 33 addresses. That’s too small to be a meaningful label; we want something closer to the hundreds - so I changed the requirement to “100 tokens of land across all projects”.
The logic itself is pretty simple, HAVING is a WHERE statement that can be used after the GROUP BY - so we don’t need another subquery. nft.trades has all chains, so we filter for only Ethereum.
We then put the data into the format of the labels schema so that it’s ready to be added to the labels repo later on.
We can query against this query (similar to if it was in labels.all but this way its just a view). Looking at the trading volume of Metaverse Landlords, we can see that they’ve made $250m worth of buying volume in a recent week - up from less than $100m the last two months.
If you were planning some metaverse land project drop, using this label would help tell you that the momentum of your target buyers has picked up. Another good view would be getting the total wallet balances across all landlords.
This is not the best execution of the label - it doesn’t contain mints! Go improve it or choose one of the other categories we’ve listed in the competition - try and follow the same process I did of finding relevant queries and dashboards and working from there.
This is just a snippet into the power of labels into making analysis easier and more composable. I’ll be showcasing the best label examples of that after the hackathon!
Bonus question (Hackathon 💸!!!):
This week, you should go and create a label query yourself! Go and submit one to the hackathon before 3/5/2023 midnight EST, I’ll be in the Dune discord with many others helping out all weekend.
Even if you’re seeing this after the hackathon, go create a label and submit to the #labels channel in the Discord (and dm me)! If you’re more advanced, go straight to submitting a PR in spellbook.
Hope you learned something! Don’t forget you can submit/vote on future week queries here.
