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:
- Blur
- 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:
ID | Name | Additional Information |
---|---|---|
1279 | nft_collection_min_price | more Information |
1280 | nft_collection_max_price | more Information |
1281 | nft_collection_avg_price | more Information |
1282 | nft_collection_trades_count | more Information |
1283 | nft_collection_min_price_usd | more Information |
1284 | nft_collection_max_price_usd | more Information |
1285 | nft_collection_avg_price_usd | more Information |
1288 | nft_market_volume | more Information |
1289 | nft_market_count | more Information |
1290 | nft_collection_holders_balance | more Information |
1291 | nft_network_profit_loss | more Information |
1292 | nft_network_profit_loss_usd | more Information |
1293 | nft_collection_profit_loss | more Information |
1294 | nft_collection_profit_loss_usd | more Information |
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