Finding all NFTs (ERC721) held by an address (feat. Dune Analytics)

Greetings!

👤 Joshua Kim

👔 Data Analyst
🎓 Master in Data Science
🎓 Bachelor in Finance
🎓 Bachelor in Philosophy
📧 joshua.kim@crossangle.io
🌎 LinkedIn
⚫ Medium

🌐 REFERENCES

📝 OpenZeppelin Docs
🟠 Dune Dashboard (created by Joshua)

🎤 Speaking @Ethcon Korea 2023

📽️ Youtube Video Clip (6:19:30)

Contract = Functions + Events

Functions

When you call a function:

• 1️⃣ it returns a value, or
• 2️⃣ it triggers an event. ( = emits an event. )

Events

When an event is emitted:

• 1️⃣ it creates and broadcasts a TX message, then
• 1️⃣ the TX message leads to state change of Ethereum.

ERC721 Interface

Functions

def balanceOf(owner):
	return "Number of Tokens held by the Owner's Account"
def ownerOf(tokenId):
	return "Owner's Address holding the Token"
def transferFrom(from, to, tokenId):
	emit Transfer
def approve(to, tokenId):
	emit Approval
...

Events

event Transfer(from, to, tokenId):
	# "tokenId" token is transferred from "from" to "to".
event Approval(owner, approved, tokenId):
 # "owner" enables "approved" to manage the "tokenId" token.
...

From Collecting Transfer Events To Calculating Wallet Balance

1️. Collect All Events Involving David.

Transfer Event #1:

• from = “Joshua”
• to = “David”
• tokenId = 1234

Transfer Event #2:

• from = “Angela”
• to = “David”
• tokenId = 5678

Transfer Event #3:

• from = “David”
• to = “Angela”
• tokenId = 1234

2️. Then Sum Up All the Events to Determine Wallet Balance.

Tokens List David currently owns:

• tokenId = 5678

1️. Collect All Transfer Events.

Dune Analytics

WITH
CTE_all_transfers AS (
    SELECT
        contract_address,
        tokenId,
        evt_tx_hash,
        "to",
        ROW_NUMBER() OVER (
            PARTITION BY contract_address, tokenId
            ORDER BY evt_block_number DESC, evt_index DESC
        ) AS recent_idx
    FROM
        erc721_ethereum.evt_Transfer
    WHERE
        contract_address IS NOT NULL
),

Query Results

2️. Filter Out the Most Recent Transfer Events for Each Token.

🔆 So that we can see who owns the tokens at the moment!

CTE_last_transfers AS (
    SELECT
        *
    FROM
        CTE_all_transfers
    WHERE
        recent_idx = 1
),

3️. Filter the Results to Show Only Those Related to the Specific Address.

🔆 So that we can see all tokens that the address owns at the moment.

CTE_last_transfers_cohort AS (
    SELECT
        *
    FROM
        CTE_last_transfers
    WHERE
        "to" = {{address}}
),

4️. Determine the Acquisition Price of the Tokens and Label the Contracts.

👨🏻‍🏫 Determine the Acquisition Price of the Tokens.

• JOIN hash with evt_tx_hash and SELECT value.

Dune Analytics

👨🏻‍🏫 Label the Contracts.

Dune Analytics

• JOIN contract_address with contract_address and SELECT name.

Dune Analytics

👨🏻‍🏫 Final Query to Wrap Up

CTE_summary AS (
    SELECT
        LABELS.name,
        TRANSFERS.contract_address,
        TRANSFERS.tokenId,
        TXS.value / 1e18 AS acquisition_cost
    FROM
        CTE_last_transfers_cohort TRANSFERS
    LEFT JOIN
        ethereum.transactions TXS
        ON TRANSFERS.evt_tx_hash = TXS.hash
    LEFT JOIN
        tokens.nft LABELS
        ON TRANSFERS.contract_address = LABELS.contract_address
            AND LABELS.blockchain = 'ethereum'
),

Case Study

Binance 7 EOA Address

☎️ 0xBE0eB53F46cd790Cd13851d5EFf43D12404d33E8

Query Results

🟠 dune.com/joshua_web3

Check it out with Etherscan just in case it’s not been correctly queried.

• Holders of Documenta X by Vuk Cosic

Etherscan

• Holders of New World Babies

Etherscan

Many Thanks!

👤 Joshua Kim

👔 Data Analyst
🎓 Master in Data Science
🎓 Bachelor in Finance
🎓 Bachelor in Philosophy
📧 joshua.kim@crossangle.io
🌎 LinkedIn
⚫ Medium

🌐 REFERENCES

📝 OpenZeppelin Docs
🟠 Dune Dashboard (created by Joshua)

🎤 Speaking @Ethcon Korea 2023

📽️ Youtube Video Clip (6:19:30)