Dune-NFT Ownership

I’ll be digging into ownership first.

To refresh some of the questions were; How many unique addresses currently own?, How many addresses owned all time? How many on average does each wallet hold? Who owns the most?

The best way to learn how to query a database is to build off the work of others. Study the way others have calculated it, modify it to fit your purpose, and enhance it with your own knowledge and ability.

To learn how to calculate ownership numbers, I studied the Pudgy Penguins Degen dashboard. Shoutout to Guy Stewart who developed this dashboard. In particular I looked at this query.

I picked the project CryptoTrunks for my practice queries and resulting dashboard. I think this is a really cool project in that the tree size is generative based on the number of transactions the wallet minting the NFT had performed in the past. It also aimed to be carbon neutral which I thought was pretty cool.

The only table used for calculating NFT ownership is erc721.”ERC721_evt_Transfer”. This makes it nice and easy.

The table is filtered by the “contract_address” fields which I could easily confirm by searching for Pudgy Penguins contract address on Etherscan, and then using the same search for CryptoTrunks.

The logic in the ownership query works as follows;

You must first assign the value of 1 to the transfers that were sent from the “mint” address “to” a “wallet” address. This is your starting quantity of NFT’s.

select “to” as wallet
,”tokenId”
,’mint’ AS “type”
, 1 as “value”
from erc721.”ERC721_evt_Transfer”
where “contract_address” = (‘\x375ea781c49eafedde07afe6196f885761f166ae’) (CryptoTrunk contract address)
and “from” =’\x0000000000000000000000000000000000000000' (mint address)

Then you assign the value of -1 to transfers that were NOT sent from the “mint” address but were sent “from” another “wallet”. Note how we alias the “from” column as “wallet” compared to the first query where we used the “to” column. So a wallet that minted the original(1), and also sent it to another wallet(-1) will have 0 value when we add the “value” column together.

select “from” as wallet
,”tokenId”
,’loss’ AS “type”
, -1 as “value”
from erc721.”ERC721_evt_Transfer”
where “contract_address” = ‘\x375ea781c49eafedde07afe6196f885761f166ae’
and “from” !=’\x0000000000000000000000000000000000000000'

But with every transfer we also need to account for which “wallet” that token was sent “to”. So the third table we assign a value of 1 to the “to” “wallet” of every transfer that once again did NOT come from the mint address.

select “to” as wallet
,”tokenId”
,’gain’ AS “type”
, 1 as “value”
from erc721.”ERC721_evt_Transfer”
where “contract_address” = ‘\x375ea781c49eafedde07afe6196f885761f166ae’
and “from” !=’\x0000000000000000000000000000000000000000'

We concatenate these queries together and assign to a CTE (with) that we call “transfers”.

Our next to step to aggregate the “value” across each “wallet” which I assign to another CTE called “transfer_sum”.

transfer_sum as (
select “wallet”,
sum(value) as “value”
from transfers
group by “wallet”

The final query is where we finally derive all of the metrics we discussed in the last post.

select
count(distinct “wallet”) filter (where “value” >0) as unique_addresses_current
,count(distinct “wallet”) as unique_addresses_all_time
, count(distinct “wallet”) filter (where “value” =1) as single_owner_addresses
, avg(“value”) filter (where “value” >0) as average_owned_per_address
, max(“value”) as most_owned_single_address
, ttl.collection_count
from transfer_sum
cross join (select count(distinct “tokenId”) as collection_count from transfers) ttl
group by ttl.collection_count

Any “wallet” with a final summed “value” >0 is a current owner of a token. Using the distinct command will ensure that address is unique.

Counting all “wallets”, regardless of current “value” indicates how many unique wallets all time.

Counting “wallets” where “value” equals 1 will result in all single wallet addresses.

Averaging the “value” of “wallets” currently holding a token tells us on average how many tokens are held per wallet.

The max “value” in the table is the largest single wallet, which in the case of CryptoTrunks is fairly substantial..over 6000 tokens.

I was also interested in displaying the total number of tokens, so I cross joined a distinct count of “token_id” from my original “transfers” table.

From each of my calculations I created counter visualizations.

I then imported those into a newly created dashboard I originally titled CryptoTrunks to reserve the URL, but then titled it “In Progress” in case anyone were to navigate to it looking for answers.

I am excited to continue to build further. Now that I understand Ownership, I think I am ready to move onto Trading Volume.

The related query for this post can be found here →https://dune.xyz/queries/122900

The dashboard can be found here →https://dune.xyz/mtitus6/CryptoTrunks