Dune-NFT Trading Volume
Queries for this post can be found at the links below;
Cumulative By Day →https://dune.xyz/queries/129105
Total →https://dune.xyz/queries/129121
Volume By Week → https://dune.xyz/queries/129793/255121
In the last post, I described how to calculate Ownership for an NFT project.
Moving on, I wanted to look at calculating Trading Volume.. These metrics circle around the question, is there still current demand for the project, or are people no longer interested in trading?
To begin learning the SQL behind this metric, I flipped over to the Bored Ape Yacht Club dashboard created by @rechase. In particular I studied this query. I really like the way the developer classified the purchases in price brackets, but since CryptoTrunks is still relatively low volume I eventually veered from that path.
The main tables for volume are opensea.”WyvernExchange_call_atomicMatch_”, and opensea.”WyvernExchange_evt_OrdersMatched”. As you might notice these volumes are for OpenSea only, but given the current dominance in the market I think this will get us close enough.
To begin, you start with the “atomicMatch” table where you get the tx_hash, and value of transfers on OpenSea. There is an addrs column which is actually a list of values separated by comma’s. You must designate which column from the list you want to return.
The 5th item (addrs[5]) in the list represents the NFT contract address which we will use in the WHERE statement.
The 7th item (addrs[7]) represents the value of the transfer. The value of the transfer is represented by either the USDC, WETH, or mint address (same as we saw in ownership query). In the later part of the query we will need to convert this token to the USD value.
We must substitute the mint address with the WETH token, since the mint address has no USD value associated with it.
The first query ends up looking like this, which I wrapped in a CTE called token
select call_tx_hash as tx_hash,
case
when addrs[7] = ‘\x0000000000000000000000000000000000000000’ then ‘\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2’ — weth
else addrs[7] — usdc or weth
end AS token_address
from opensea.”WyvernExchange_call_atomicMatch_”
where addrs[5] = ‘\x4aD2d817B60db6ee79C1Ae016429251A0d25423F’ — contract address
The next step is to join our CTE ,“token”, with the other core table “OrdersMatched” using the “tx_hash” column. I think there must be bid data included in “atomicMatch” which is why you actually have to check that the order was matched.
from opensea.”WyvernExchange_evt_OrdersMatched” om
inner join token on token.tx_hash = om.evt_tx_hash
At the same time, we calculate our value. With the transaction hash,contract address, and time of transaction we can ping our “prices” table to get an idea of value.
When studying the BYAC query I learned a new table (yay!). The erc20.tokens table can tell us the “decimals” needed to convert “price” field to the actual token value we are used to seeing.
The “prices” table is joined on contract address and minute.
inner join erc20.tokens erc on token.token_address = erc.contract_address
inner join prices.usd prices on prices.minute = date_trunc(‘minute’, evt_block_time)
AND token.token_address = prices.contract_address
The final query to calculate value each “day” ends up looking like;
select date_trunc(‘day’, evt_block_time) AS “day” — transfer day
, sum(om.price / 10^erc.decimals) as “eth” — price ETH
, sum((om.price / 10^erc.decimals) * prices.price) as “usd” — price USD
from opensea.”WyvernExchange_evt_OrdersMatched” om
inner join token on token.tx_hash = om.evt_tx_hash
inner join erc20.tokens erc on token.token_address = erc.contract_address
inner join prices.usd prices on prices.minute = date_trunc(‘minute’, evt_block_time)
AND token.token_address = prices.contract_address
As I typed this post and looked at this query, I realized there was a gap. The “token_address” in the “tokens” CTE was NOT JUST WETH, but we actually have USDC in there too. If we join “prices” on “contract_address”=USDC then we can NOT report om.price/10^erc.decimals as ETH alone. A 100 USDC sale will read as a 100 ETH sale.
This may not have impacted BYAC if no one transacted in USDC, but in the case of CryptoTrunks, we had about 20 USDC transactions that inflated the ETH amount substantially.
I fixed the query by adding a CASE statement and additional subselect. I needed the daily ETH/USD price, and if the transaction took place in USDC then I must divide by this value to get the ETH value.
select date_trunc(‘day’, evt_block_time) AS “day” — transfer day
, sum(case when token_address = ‘\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2’ then om.price / 10^erc.decimals — weth
when token_address = ‘\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48’ then (om.price / 10^erc.decimals)/eth_prices.price — usdc converted to weth
else 0 end) as “eth” — price ETH
, sum((om.price / 10^erc.decimals) * prices.price) as “usd” — price USD
from opensea.”WyvernExchange_evt_OrdersMatched” om
inner join token on token.tx_hash = om.evt_tx_hash
inner join erc20.tokens erc on token.token_address = erc.contract_address
inner join prices.usd prices on prices.minute = date_trunc(‘minute’, evt_block_time)
AND token.token_address = prices.contract_address
inner join (SELECT max(prices.price) as price, prices.minute from prices.usd prices where contract_address = ‘\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2’ group by prices.minute)
eth_prices on eth_prices.minute = date_trunc(‘minute’, evt_block_time)
Whew, this is getting hard to put into words, but hopefully that makes sense.
Now that we have ETH and USDC value per day it just comes down to how we want to display it. I chose to display a cumulative by day chart first.
To get the cumulative chart I turned the above query first into a CTE I called “volume”. Then, going back to our value over time post, I used the OVER expression to build a cumulative chart by day.
select “day”
, sum(“eth”) over (order by “day” asc) as “cum_ETH”
, sum(“usd”) over (order by “day” asc) as “cum_USD”
from volume
Once you understand the base query using our core tables it is not very hard to create different variations of the dame data.
I added counters to show “total_ETH” and “total USDC” ignoring the OVER “day” portion.
To create this, I forked the query above, and went back to my “value” CTE to eliminate the “day” in the SELECT and GROUP BY clauses. Since my result now exists in the “value” CTE, it no longer needs to be a CTE and I can just return its results, and delete everything after.
I also, just wanted to show the total ETH per week. Again, I forked the starting query, and replaced every mention of “day” with “week”. I was not interested in Cumulative here (seemed redundant) so again I removed the “value” CTE and deleted everything after.
These metrics really put into perspective how much trading volume is related to this project. I think this a valuable insight to see the active interest in a project, and how “early” you might actually be.
There was a big bump of activity in August, but since then it has garnered less interest. You could interpret this one of two ways…either the project is “dead” or yet to “explode”, which I lean towards the latter.
