Web3 SQL Weekly #3: Finding all NFTs (ERC721) held by an address
We get NFT balances and also track how the token was acquired (mint, bought, gifted) as well as the value paid.
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.
The video goes much more in the depth on explanations and examples, but you can also just read the written overview below:
What:
Today I’ll cover how to get all NFTs held by an address (just ERC721). This will also include filtering for gifted, minted, versus bought tokens. We’ll also have some value approximations to make it easier to filter things.
Blockchain concepts:
First we need to cover what an ERC721 is. An ERC721 is really similar to an ERC20 in terms of approving and then transferring, as well as minting and burning with some total supply. The key difference is that each token has it’s own tokenId, meaning that all tokens are distinct and thus non-fungible (hence the name NFT). You can’t have more than one of each tokenId at a time (if you do, that’s an ERC1155 - which we’ll cover some other time).
The only event you need to know from this standard is: Transfer(from, to, tokenId), which is emitted any time the token is transferred from one address to another address.
All these tokens of the same collection share the same contract address. Etherscan’s token view will quickly show you the total supply (number of tokens minted, it’s common to mint 10k collections).
As always, Open Zeppelin is the best resource for exploring different implementations of ERC721. There have been many iterations of the standard that optimize on gas costs among other things, such as ERC721A in the contract referenced above. You might have seen EIP712 also thrown around a lot - that’s a signature method, don’t confuse that with ERC721.
A single ERC721 contract can also host many different collections though - depending on how metadata is stored. Artblocks started off by hosting their first dozen or so collections on the same contract. Opensea, Zora, Foundation, and others used to do shared collections from different artists on the same contract too. This pattern has largely shifted to factory editions/drops so that artists get more ownership and control of their contracts.
Anyways, I won’t go deeper into how metadata is stored or managed today - that’s enough context for us to get started!
SQL Query Walkthrough:
Here’s the query we’re going to create. We’ll be looking at Deeze’s wallet for this one.
Getting NFT balances is actually easier than ERC20 or ETH (native token) balances, because you just need to track what address each tokenId was last transferred to. The easiest way to do this is by using the row_number() window function. This function counts up from 1, based on some partition and order. The order tells the function how to order the column(s) to count upwards from, and a different count is started for each partition column set.
Let’s write out the query, and then I’ll explain things in depth.
So here we’re selecting from the nft.transfers table, where we filter for ethereum using the “{{chain}}” parameter. We’re only looking at ERC721 standards in this query, to keep it simple. This table keeps track of every time an NFT was transferred (by using the Transfer event).
Now for the row_number(): we’re partitioning by contract_address and token_id, because we want to get a new count going for each token on each contract. We then order by block_number and evt_index descending, because we only want the most recent transfer. Sometimes a token is transferred multiple times in one block, so we need evt_index to ensure that the count starts from the last transfer of a token from a contract in the block.
Next, let’s get the last transfer for each token/contract where the receiver (to) was the address we’re getting the balances of.
We put the last part into a CTE, and then perform a SELECT where we’re only keeping the transfers that were the last for any given token_id and contract_address, and then only keeping the transfers to some address (which we’ve parameterized but have put Deeze’s address in as the default for now).
So technically, this already gives you all the NFTs (ERC721) held by an address! However, we want more metadata around the collection and how the token was acquired.
To do that, we need to join in the tokens.nft and ethereum.transactions tables.
It looks confusing, but I promise it’s not too bad. tokens.nft (spellbook table) contains collection details for most NFTs on across chains. So we join on specifically the contract_address of the transfer and the blockchain we’re looking into to pull the metadata for symbol, name, and category.
We also join ethereum.transactions (as {{chain}}.transactions, to easily switch chains we’re querying) on the transaction hash of the transfer to get the signer (tx.”from”) and the value ETH spent by the signer (tx.value divided by 10^18). Here’s how we decipher the acquisition method in CASE WHEN:
If the signer was not the NFT receiver, then that means someone gifted the token to them.
CASE WHEN evaluates sequentially, so making it to line 28 means that tx.“from” = {{address}} of the receiver (this is same as us putting lhu.to, but faster since it’s a constant). So here we check if the transfer was “from” the null address, which would indicate this was a mint. Note that transfer “from” and tx.”from” are two different things!
If it wasn’t gifted or minted, then that means it was bought from someone else - so that’s the default ‘else’ value.
The acquisition_value_native is really just an approximation for mint or bought value, and doesn’t really apply to gifts.
With that, you’re done! We can see Deeze here has 4,796 NFTs (ERC721) as of 2/2/2023, and you can do some fun searches through acquisition types to see which ones were the most expensive - assuming it was minted/bought using ETH. Seems like he’s a fan of Art Blocks (at least from this wallet).
Bonus question:
Can you think of a better way of getting the value for bought and minted tokens?
Hint 1: you can join on nft.trades by transaction hash plus a few other columns. You can’t do only transaction hash because there might be many tokens being bought in the same transaction!
Hint 2: to get more accurate on minted tokens, you’ll need to divide the transaction value by number of mints.
Hope you learned something! Don’t forget you can submit/vote on future week queries here.
