NFT Tables overview

Overview

NFT stands for Non-Fungible Token, which is a digital asset that represents ownership of a unique item or piece of content, like art, music, video, or tweets. NFTs are different from cryptocurrencies because each NFT is unique and can't be exchanged one-to-one, while cryptocurrencies are interchangeable.

NFTs are made using blockchain technology, which provides a secure and transparent record of ownership and transfer, allowing creators and owners to prove ownership, authenticity, and rarity, potentially increasing the value of their digital assets over time.

In Santiment Queries, we collect data for all NFTs from exchanges on:

Ethereum:

• Cryptopunks
• Foundation
• Looksrare
• Opensea
• Rarible
• Superrare

Polygon:

• Opensea_polygon

This information gives you the ability to easily track an NFT of interest or an entire collection, as well as investigate past prices and owners.

List of nft tables

All our tables that contain NFT-related data have the string 'nft' in their name. So one easy way to find them yourself is by running the following query:

1
SHOW TABLES LIKE '%nft%'

1
2
3
4
5
┌─name───────────────────┐
| nft_trades             |
| nft_tokens_metadata    |
| intraday_nft_metrics   |
└────────────────────────┘

Exploration of the tables

We can inspect the tables with the commands we learnt in Exploration

Below we have listed all the nft tables with thier columns, a brief descriptions and some example queries which you can use to explore the tables.

nft_trades

The nft_trades is a table designed to store information about the NFT trades that we have collected. It has the following columns:

• dt (DateTime): When the transaction happened.

• blockchain (String): On which blockchain.

• tx_hash (String): Unique identifier that is generated when the transaction was executed.

• log_index (UInt32): Log index of trade event.

• platform (String): Name of platform where trades occured such as opensea, cryptopunks and etc.

• seller_address (String): Address who sold the nft.

• buyer_address (String): Address who bought the nft.

• nft_contract_address (String): Address of the nft's collection.

• currency_contract_address (Nullable(String)): Identifier of the currency used in the trade. It is empty if native coin was used (ether or matic), otherwise it is the address of the smart contract of the ERC20 token.

• asset_ref_id (UInt64): Reference ID of the currency contract that can be used to retrieve additional information about the asset from the asset_metadata table.

• token_ids (Array(String)): Array of those NFT token ids in hex which were exchanged in the trade. Usually only one is exchanged.

• amount_tokens (Array(String)): Array of amounts, which describes how many tokens were transfered.

• nft_contract_type (UInt8): Type of nft contract, 0 - erc721, 1 - erc1155. Based on signature of transfer.

• amount (String): The amount of the transaction in the asset used in it.

• complete (UInt8): State of the transaction. (1 means it is completed).

• computed_at (DateTime): The timestamp when the trade was inserted to the table.

nft_tokens_ metadata

The nft_tokens_metadata is a table designed to store information about NFT tokens. It has the following columns:

• blockchain (String): Refers to the decentralized platform on which the NFT exists, e.g. Ethereum, Binance Smart Chain, etc.

• address (String): Represents the unique public address of the NFT owner.

• token_id (String): Unique identifier assigned to each NFT in decimal, used to distinguish it from others.

• uri (Nullable(String)): Uniform Resource Identifier, a link that directs to additional information or media related to the NFT.

• data (Nullable(String)): Contains the NFT's metadata, such as its name, image, and any other relevant information in JSON format.

• created_at (DateTime): The date and time when the NFT was created.

• error (Nullable(String)): Any error message associated with the NFT, such as a transaction failure, if applicable. This column is used to track and monitor any issues that arise in the NFT creation process.

intraday_nft_metrics

The intraday_nft_metrics is a table designed to store intraday metrics for NFT assets. The table has the following columns:

• asset_id (UInt64): ID of the currency used in value calculation(ETH or WETH).

• metric_id (UInt64): Unique identifier assigned to the specific metric being recorded.

• address (String): Represents the address of the NFT collection.

• collection_name (String): The name of the NFT collection that the asset belongs to.

• token_id (Nullable(UInt64)): Unique identifier assigned to each NFT, used to distinguish it from others.

• dt (DateTime): The date and time when the metric was recorded.

• value (Float64): The value of the metric recorded at the specified dt.

• computed_at (DateTime): The timestamp when the trade was inserted to the table. This is used to track the accuracy of the metric calculation and to ensure that the most up-to-date information is available.

There are currently 16 metrics we track:

Sample Queries

Number of unique NFT owners

• Check the number of unique wallets that have owned an NFT in the last 7 days.

1
2
3
SELECT countDistinct(buyer_address) AS uniqueBuyers
FROM nft_trades
WHERE dt >= (now() - INTERVAL 6 DAY)

Test in Queries

Unique NFT platforms ordered by sales

• Check the platforms ordered by sales for last 7 days

1
2
3
4
5
6
7
SELECT
    platform,
    count(platform) AS sales
FROM nft_trades
WHERE (complete = 1) AND (dt >= (now() - toIntervalDay(6)))
GROUP BY platform
ORDER BY sales DESC

Test in Queries

Number of unique sales for a platform

• Sales for a platform (in this case ‘opensea’) for last 5 months split by month

1
2
3
4
5
6
SELECT
    toStartOfMonth(dt) AS month,
    count(platform) AS sales
FROM nft_trades
WHERE (complete = 1) AND (dt >= (now() - toIntervalMonth(4))) AND (platform = 'opensea')
GROUP BY month

Test in Queries

Ranking by completed purchases

• Top 10 buyers ordered by completed purchases count for last 5 days. If we want to get the top 10 sellers we can swap buyer_address with seller_address.

1
2
3
4
5
6
7
8
9
10
11
SELECT
    buyer_address,
    platform,
    count(platform) AS purchases
FROM nft_trades
WHERE (complete = 1) AND (dt >= (now() - toIntervalDay(4)))
GROUP BY
    buyer_address,
    platform
ORDER BY purchases DESC
LIMIT 10

Test in Queries

Summed amounts of assets for a platform

• Unique assets for the last 7 days for a single platform with summed amounts. We can get simmilar information for buyers and sellers if swap the platform in where with buyer/seller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    DISTINCT tb1.asset_ref_id,
    tb2.name,
    tb1.sum_amount
FROM
(
    SELECT
        asset_ref_id,
        sum(CAST(amount, 'UInt64')) AS sum_amount
    FROM nft_trades
    WHERE
    	dt >= (now() - INTERVAL 6 DAY) and platform = 'opensea'
    GROUP BY asset_ref_id
	order by sum_amount
) AS tb1
INNER JOIN asset_metadata AS tb2 ON tb1.asset_ref_id = tb2.asset_ref_id
ORDER BY sum_amount DESC

Test in Queries

Collections ranked by traded Tokens

• Collections ranked based on number of traded Tokens in collection for the last 5 days

1
2
3
4
5
6
7
8
9
10
11
SELECT 
	nft_contract_address,
	platform,
	count(token_ids) as number_of_traded_nfts
FROM nft_trades 
WHERE (complete = 1) and (dt >= NOW() - toIntervalDay(4))
GROUP BY 
	nft_contract_address,
	platform
ORDER BY number_of_traded_nfts desc
LIMIT 10

Test in Queries

Collections ranked by recorded Tokens

• Collections ranked based on number of recorded Tokens in collection

1
2
3
4
5
6
7
8
9
10
11
SELECT
	blockchain,
	address,
	COUNT(token_id) as number_of_nfts
FROM
	nft_tokens_metadata
GROUP BY
	blockchain,
	address 
ORDER BY number_of_nfts DESC
LIMIT 10

Test in Queries

All tokens with extra info for an address

• Get all the tokens and information about them for a given address

1
2
3
4
5
6
7
8
SELECT
	token_id,
	simpleJSONExtractString(data, 'name') as token_name,
	simpleJSONExtractString(data, 'description') as token_description,
	simpleJSONExtractString(data, 'external_url') as external_url
FROM nft_tokens_metadata
WHERE address = '0x76be3b62873462d2142405439777e971754e8e77'
ORDER BY token_id

Test in Queries

NFT trades in USD for an address

• Recent NFT trades volume of an address with amount calculated in usd

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT day_dt, SUM((amount/POW(10, decimals))*price) as volume_usd FROM (
    SELECT 
        toDate(dt) as day_dt,
        dictGet('default.assets_by_ref_id', 'decimals', asset_ref_id) as decimals,
        dictGet('default.assets_by_ref_id', 'asset_id', asset_ref_id) as asset_id,
        SUM(toInt256(amount)) as amount
    FROM nft_trades
    WHERE (
        buyer_address == '0x94fc70cffb47fc77d16f7997acd527e45a87b050'
        OR
        seller_address == '0x94fc70cffb47fc77d16f7997acd527e45a87b050'
    ) AND blockchain = 'ethereum' AND dt >= toDate(NOW()) - INTERVAL 7 DAY
    GROUP BY day_dt, asset_id, decimals
) INNER JOIN (
    SELECT dt as day_dt, asset_id, value as price
    FROM daily_metrics_v2
    WHERE metric_id = (SELECT metric_id FROM metric_metadata FINAL WHERE name = 'daily_avg_price_usd')
) USING day_dt, asset_id
GROUP BY day_dt

Test in Queries