fb3ce38c-7bfe-475c-bca8-3f1e44ae80a62023-08-06 11:11 AM
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
›
⌄
WITH ranked_buyers AS (
SELECT
buyer_address,
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,
NFT_COLLECTION,
ROW_NUMBER() OVER (
PARTITION BY
buyer_address
ORDER BY
BLOCK_TIMESTAMP
) AS rn
FROM
flow.core.ez_nft_sales
WHERE
NFT_COLLECTION IN ('A.e4cf4bdc1751c65d.AllDay', 'A.329feb3ab062d289.UFC_NFT', 'A.0b2a3299cc857e29.TopShot')
)
SELECT
buyer_address,
date AS first_purchase_date,
-- Mapping the NFT_COLLECTION to a human-readable Platform
CASE
WHEN NFT_COLLECTION ='A.e4cf4bdc1751c65d.AllDay' THEN 'NFL All Day'
WHEN NFT_COLLECTION ='A.329feb3ab062d289.UFC_NFT' THEN 'UFC Strike'
WHEN NFT_COLLECTION ='A.0b2a3299cc857e29.TopShot' THEN 'NBA Top Shot'
END AS Platform
FROM
ranked_buyers
WHERE
rn = 1
ORDER BY
first_purchase_date DESC;
Run a query to Download Data