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:

    IDNameAdditional Information
    1279nft_collection_min_pricemore Information
    1280nft_collection_max_pricemore Information
    1281nft_collection_avg_pricemore Information
    1282nft_collection_trades_countmore Information
    1283nft_collection_min_price_usdmore Information
    1284nft_collection_max_price_usdmore Information
    1285nft_collection_avg_price_usdmore Information
    1288nft_market_volumemore Information
    1289nft_market_countmore Information
    1290nft_collection_holders_balancemore Information
    1291nft_network_profit_lossmore Information
    1292nft_network_profit_loss_usdmore Information
    1293nft_collection_profit_lossmore Information
    1294nft_collection_profit_loss_usdmore 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