Comparison of the percentage of different play types in the total number of NFTs sold with the percentage of different play types in valuable NFTs
Zanyar_98Comparison of the percentage of different play types in the total number of NFTs sold with the percentage of different play types in valuable NFTs
Updated 2022-09-23
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 ALL_DAY_NFTs AS
(SELECT NFT_COLLECTION, NFT_ID, NFLALLDAY_ID,Serial_Number ,MOMENT_TIER ,TOTAL_CIRCULATION,MOMENT_DESCRIPTION,PLAYER, TEAM, SEASON, WEEK,
CLASSIFICATION, Play_Type,MOMENT_DATE FROM flow.core.dim_allday_metadata),
Top_NFTs as (SELECT ALL_DAY_NFTs.NFT_ID, Serial_Number, MOMENT_TIER,TOTAL_CIRCULATION ,MOMENT_DESCRIPTION,PLAYER, TEAM, SEASON, WEEK, CLASSIFICATION, Play_Type,
MOMENT_DATE, PRICE "Sale Price ($)" --TX_ID, MARKETPLACE, BUYER, SELLER, PRICE, CURRENCY, ALL_DAY_NFTs.*
FROM flow.core.ez_nft_sales AS SALES
JOIN ALL_DAY_NFTs ON (SALES.NFT_COLLECTION = ALL_DAY_NFTs.NFT_COLLECTION AND SALES.NFT_ID = ALL_DAY_NFTs.NFT_ID)
WHERE SALES.TX_SUCCEEDED ='TRUE' AND PLAYER != 'N/A' AND BLOCK_TIMESTAMP::DATE <CURRENT_DATE
ORDER BY "Sale Price ($)" DESC LIMIT 120),
Rank_By_NFT_IDs AS (SELECT NFT_ID, Serial_Number, MOMENT_TIER,TOTAL_CIRCULATION ,MOMENT_DESCRIPTION,PLAYER, TEAM, SEASON, WEEK, CLASSIFICATION, Play_Type,
MOMENT_DATE,"Sale Price ($)", RANK() OVER(PARTITION BY NFT_ID ORDER BY "Sale Price ($)" DESC) Rank
FROM Top_NFTs ORDER BY "Sale Price ($)" DESC ),
Top_100 AS (SELECT NFT_ID, Serial_Number, MOMENT_TIER,TOTAL_CIRCULATION ,"Sale Price ($)",MOMENT_DESCRIPTION,PLAYER, TEAM, SEASON, WEEK, CLASSIFICATION, Play_Type,
MOMENT_DATE FROM Rank_By_NFT_IDs WHERE RANK = '1' ORDER BY "Sale Price ($)" DESC LIMIT 100 ),
TOP_100_NUMBER_OF_PLAY_TYPE AS (SELECT Play_Type, COUNT(Play_Type) "Number of NFTs", 'Valuable NFTs' AS TYPE, COUNT(Play_Type) "Percent"
FROM Top_100
GROUP BY Play_Type
ORDER BY "Percent" ASC),
ALL_SALES AS (SELECT BLOCK_TIMESTAMP::DATE AS DAYS,ALL_DAY_NFTs.NFT_ID, Serial_Number, MOMENT_TIER,TOTAL_CIRCULATION ,MOMENT_DESCRIPTION,PLAYER, TEAM, SEASON, WEEK, CLASSIFICATION, Play_Type,
MOMENT_DATE, PRICE "Sale Price ($)", RANK() OVER(PARTITION BY ALL_DAY_NFTs.NFT_ID ORDER BY "Sale Price ($)" DESC) Rank --TX_ID, MARKETPLACE, BUYER, SELLER, PRICE, CURRENCY, ALL_DAY_NFTs.*
FROM flow.core.ez_nft_sales AS SALES
JOIN ALL_DAY_NFTs ON (SALES.NFT_COLLECTION = ALL_DAY_NFTs.NFT_COLLECTION AND SALES.NFT_ID = ALL_DAY_NFTs.NFT_ID)
WHERE SALES.TX_SUCCEEDED ='TRUE' AND PLAYER != 'N/A' AND BLOCK_TIMESTAMP::DATE <CURRENT_DATE
ORDER BY "Sale Price ($)"),
Unique_NFTS AS ( SELECT DAYS, NFT_ID, Serial_Number, MOMENT_TIER,TOTAL_CIRCULATION ,MOMENT_DESCRIPTION,PLAYER,
TEAM, SEASON, WEEK, CLASSIFICATION, Play_Type, MOMENT_DATE, "Sale Price ($)"
FROM ALL_SALES WHERE RANK = 1
),
Run a query to Download Data