SandeshCurrent holders top
Updated 2025-01-13
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
›
⌄
⌄
/*
This query identifies the current holders of a specific NFT contract and counts the number of distinct NFTs each holder possesses.
Steps:
1. `current_holders` CTE:
- Retrieves the most recent transfer for each token, effectively identifying the current owner.
- Uses `ROW_NUMBER()` window function to select the latest transfer per token based on `block_timestamp`.
2. Final SELECT:
- Aggregates the data to count how many distinct NFTs each user currently holds.
- Results are sorted by the number of NFTs held, in descending order.
*/
WITH current_holders AS (
-- Identify the most recent transfer for each token to determine the current owner
SELECT
*
FROM ethereum.nft.ez_nft_transfers
WHERE nft_address = LOWER('0xDE76aD8998310dd4C6cA9fdb03a5F20bbf01Ce96')
QUALIFY (ROW_NUMBER() OVER (PARTITION BY tokenid ORDER BY block_timestamp DESC) = 1) -- Select only the latest transfer for each token
)
SELECT
nft_to_address AS user_address,
COUNT(DISTINCT tokenid) AS number_of_nfts
FROM current_holders
GROUP BY user_address
ORDER BY number_of_nfts DESC;
QueryRunArchived: QueryRun has been archived