NFT analytics with Dune

Where is data stored?

erc721."ERC721_evt_Transfer"

Contains a record for every ERC-721 NFT that is transferred from one wallet to another, could be a mint, could be a secondary sale, could be a swap, or just moving between wallets.

nft.trades

Contains a record for every sale on marketplaces such as OpenSea and Rarible. The following Dune guide explains this table in detail:
https://docs.dune.xyz/data-tables/data-tables/nft.trading

ethereum.transactions and ethereum.logs

Every NFT transfer or trade will have an associated transaction record (with corresponding logs).

usd.prices

Stores current and historical USD amounts for ERC20 tokens.

Tips for querying

The following Dune guide provides some tips for querying:
https://docs.dune.xyz/about/tutorials/dune-guides/tips

The most important thing to note is that Ethereum addresses are stored in Dune with a \x prefix instead of 0x. So queries must be in the form

WHERE address = '\x6b175474e89094c44da98b954eedeac495271d0f'

NFT Ownership

NFT ownership can be determined using the erc721."ERC721_evt_Transfer" table.

NFTs owned by wallet

WITH wallet AS (
    SELECT CONCAT('\',LTRIM('{{wallet_address}}', '0'))::bytea AS address
),

all_721 AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY "contract_address", "tokenId" ORDER BY "evt_block_time" DESC) AS rn
    FROM erc721."ERC721_evt_Transfer", wallet
    WHERE "from" = wallet."address" OR "to" = wallet."address"
)

SELECT "contract_address", "tokenId"
FROM all_721, wallet 
WHERE rn = 1
AND "to" = wallet."address"

View Dune query

Current NFT owners

WITH contract AS (
    SELECT CONCAT('\',LTRIM('{{contract_address}}', '0'))::bytea AS address
),

current_owners AS (
    SELECT DISTINCT ON ("tokenId") 
        "to" AS wallet, "tokenId"
    FROM erc721."ERC721_evt_Transfer", contract
    WHERE "contract_address" = contract."address"
    ORDER BY "tokenId", "evt_block_time" DESC
)

SELECT wallet, COUNT(*) AS owned
FROM current_owners
GROUP BY wallet
ORDER BY owned DESC

View Dune query

Unique NFT owners over time

WITH contract AS (
    SELECT CONCAT('\',LTRIM('{{contract_address}}', '0'))::bytea AS address
),

days AS (
    SELECT generate_series(date_trunc('day', MIN("evt_block_time"))::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY
    FROM erc721."ERC721_evt_Transfer", contract
    WHERE "contract_address" = contract."address"
),

owners_by_day as (
    SELECT * 
    FROM days d JOIN LATERAL (
        SELECT DISTINCT ON ("tokenId") 
            "to", "tokenId", "evt_block_time"
        FROM erc721."ERC721_evt_Transfer", contract
        WHERE "contract_address" = contract."address"
        AND date_trunc('day', "evt_block_time")::date <= d."day"
        ORDER BY "tokenId", "evt_block_time" DESC) owners ON true
)

SELECT COUNT(DISTINCT "to"), "day"
from owners_by_day
group by "day"

View Dune query

Mints

The nft.trades table does not include mints so we need to use the erc721."ERC721_evt_Transfer" table to find mint transfers and the ethereum.transaction table to get the mint price (assuming ETH is used).

NFT mints

WITH contract AS (
    SELECT CONCAT('\',LTRIM('{{contract_address}}', '0'))::bytea AS address
)

SELECT *
FROM erc721."ERC721_evt_Transfer", contract
WHERE "contract_address" = contract."address" AND "from" = '\x0000000000000000000000000000000000000000'

View Dune query

NFT mints with prices

WITH contract AS (
    SELECT CONCAT('\',LTRIM('{{contract_address}}', '0'))::bytea AS address
)

SELECT COUNT(*) as tokens, (t.value/COUNT(*)) / 10^18 as avg_value_per_token, t.hash
FROM erc721."ERC721_evt_Transfer" e
JOIN ethereum."transactions" t ON t.hash = e.evt_tx_hash, contract c
WHERE e."contract_address" = c."address" AND e."from" = '\x0000000000000000000000000000000000000000'
GROUP BY hash, t.value
ORDER BY tokens DESC

View Dune query

Marketplace Sales

For marketplace sales the nft.trades table generally has everything you need. It contains the amount paid in the original currency (e.g. ETH) and the equivalent USD amount at the time of sale. If you require the USD amounts at the latest rates then the usd.prices table required.

NFT Volume Traded

WITH contract AS (
    SELECT CONCAT('\',LTRIM('{{contract_address}}', '0'))::bytea AS address
),

days AS (
    SELECT generate_series(date_trunc('day', MIN("block_time"))::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY
    FROM nft.trades, contract
    WHERE nft_contract_address = contract."address"
),

daily_sales AS (
    SELECT
        d.day, 
        sum(t.original_amount) as total
    FROM nft.trades t
    JOIN days d on d.day = date_trunc('day', t.block_time), contract
    WHERE nft_contract_address = contract.address 
    AND original_currency IN ('ETH', 'WETH')
    AND number_of_items = 1
    GROUP BY d.day
)

SELECT day, sum(total) OVER (ORDER BY day) AS total
FROM daily_sales
ORDER BY day

View Dune query

NFT Floor Price

WITH contract AS (
    SELECT CONCAT('\',LTRIM('{{contract_address}}', '0'))::bytea AS address
)

SELECT
    date_trunc('day', block_time) AS "Time",
    percentile_cont(.100) WITHIN GROUP (ORDER BY original_amount) AS "Floor"
FROM nft.trades, contract
WHERE nft_contract_address = contract.address 
AND original_currency IN ('ETH', 'WETH')
AND number_of_items = 1
GROUP BY "Time"
ORDER BY "Time" DESC

View Dune query