DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for Charting OpenSea Volume for NFTs: Dune vs Sort
Sort
Sort

Posted on

Charting OpenSea Volume for NFTs: Dune vs Sort

Sort is an API-first version of Dune. What Sort lacks in functionality compared to Dune, it makes up for in speed and UI customization. The Alpha version of the Sort API was released on April 12th, 2022, for any bugs or feature enhancement requests, please feel free to jump into the Discord.

The Sort API is also open and free, and doesn't require any API keys.

Dune uses SQL, which is a powerful query language that has been around since the 1970s. Although SQL is powerful, it can become complex over time, especially as the number of tables and relationships between those tables grow. Here is the Dune SQL query for OpenSea volume (reported by their most popular OpenSea dashboard):

WITH token AS
  (SELECT DISTINCT call_tx_hash AS tx_hash,
                   CASE
                       WHEN addrs[7] = '\x0000000000000000000000000000000000000000' THEN '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
                       ELSE addrs[7]
                   END AS token_address
   FROM opensea."WyvernExchange_call_atomicMatch_"
   WHERE (addrs[4] = '\x5b3256965e7c3cf26e11fcaf296dfc8807c01073'
          OR addrs[11] = '\x5b3256965e7c3cf26e11fcaf296dfc8807c01073')
     AND call_success),
     excluded_txns AS
  (SELECT call_tx_hash
   FROM opensea."WyvernExchange_call_atomicMatch_"
   WHERE (addrs[4] = '\x5b3256965e7c3cf26e11fcaf296dfc8807c01073'
          OR addrs[11] = '\x5b3256965e7c3cf26e11fcaf296dfc8807c01073')
     AND call_success
     AND call_block_time > '2022-01-21'
   GROUP BY 1
   HAVING count(DISTINCT CASE
                             WHEN addrs[7] = '\x0000000000000000000000000000000000000000' THEN '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
                             ELSE addrs[7]
                         END) > 1)
SELECT date_trunc('day', evt_block_time) AS DAY,
       SUM((om.price / 10^erc.decimals) * p.price) AS 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 p ON p.minute = date_trunc('minute', evt_block_time)
AND maker != taker
AND token.token_address = p.contract_address
AND date_trunc('day', p.minute) > date_trunc('day', now()) - interval '90 days'
AND evt_tx_hash not in
  (SELECT *
   FROM excluded_txns)
GROUP BY 1
ORDER BY 1
Enter fullscreen mode Exit fullscreen mode

Simple right?

Sort takes a different approach, automatically storing every fully decoded blockchain transaction (logs and all) in a single location, allowing for very simple queries. We can form an API request to Sort for OpenSea volume data just with the contract address and function name. If you're not familiar with the OpenSea contract, sales are made via the 'atomicMatch_' function.

POST https://api.sort.xyz/v0/transaction
Enter fullscreen mode Exit fullscreen mode

POST body:

{
  "contract_address": "0x7f268357A8c2552623316e2562D90e642bB538E5",
  "contract_function": "atomicMatch_",
  "aggregate" : {
    "op": "sum",
    "by": "hour",
    "field": "value.eth"
  },
  "limit": 24
}
Enter fullscreen mode Exit fullscreen mode

The API call above matches on the "0x7f268357A8c2552623316e2562D90e642bB538E5" contract address (OpenSea) and the "atomicMatch_" function (used when an NFT trade is performed). The "aggregate" field sums up the "value.eth" field by hour, and returns the last 24 hours of data based on the "limit" specified.

The API is new, and you may notice it doesn't do a few things the Dune API does, such as return results in US dollars. However, we're working fast and hope to catch up in functionality very quickly.

Note: When an OpenSea transaction is performed in WETH (instead of ETH), the value field is 0, however, Sort stores the ETH equivalent of WETH in the "nft.value_eth" field (along with ETH), which can also be used for aggregations.

View the complete Javascript example for charting OpenSea Volume here:

We hope you'll continue on to learn more about the Sort API.

Top comments (0)

All DEV content is created by the community!

Hey, if you're landing here for the first time, you should know that this website is a global community of folks who blog about their experiences to help folks like you out.

Sign up now if you're curious. It's free!