SandeshOther NFTs held
Updated 2024-10-21
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
/*
This query examines the current holders of a specific NFT contract and identifies other NFTs they hold. It aims to determine which other NFT collections are popular among these users, focusing on NFTs with a history of transactions above a certain price.
Steps:
1. `holders` CTE:
- Identifies the current holders of the specified NFT contract by finding the most recent transfer for each token.
- Uses `ROW_NUMBER()` to select only the latest transfer per token based on `block_timestamp`.
2. `other_nft_held` CTE:
- Filters for other NFTs held by the users identified in the `holders` CTE.
- Limits to contracts that have recorded transactions with prices above $50, suggesting more significant or valuable holdings.
3. Final SELECT:
- Aggregates data to determine the total number of NFTs held and the count of distinct users holding those NFTs.
- Applies custom labels for specific contracts (e.g., 'ENS', 'Fableborne Primordials') and groups other collections under 'others'.
- Excludes contracts that are the same as the primary NFT contract or known irrelevant addresses.
- Filters results to focus on NFTs that have been traded above $10 within the last month and are held by more than 2 users.
- Orders by the number of distinct users holding each NFT and limits results to the top 20.
*/
WITH holders AS (
-- Identify the most recent holder for each token in the specified NFT contract
SELECT
nft_to_address
FROM ethereum.nft.ez_nft_transfers
WHERE nft_address = LOWER('0xde76ad8998310dd4c6ca9fdb03a5f20bbf01ce96')
AND block_number >= '19524912'
QUALIFY (
ROW_NUMBER() OVER (
PARTITION BY tokenId
ORDER BY block_timestamp DESC
) = 1 -- Select only the latest transfer for each token
)
),
QueryRunArchived: QueryRun has been archived