elvisNFTX Q1.2 22/6/12 Volume Tser for TOP9
Updated 2022-06-12
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
›
⌄
⌄
/*
Q1. Which 3 NFT collections have the most total sales volume in the last 60 days on NFTX? These are transactions where someone redeems their NFTX tokens for a specific NFT.
Hint: You can find these sales in ez_nft_sales. Visualize your findings.
*/
WITH NFTX_Proj_Vol AS (
SELECT project_name, sum(price) as volume_nftx_token, sum(price_usd) AS volume_usd, sum(platform_fee_usd) AS platform_fees_usd, sum(creator_fee_usd) as creator_fees_usd,
sum(total_fees_usd) AS total_fees_usd, sum(tx_fee_usd) AS Tx_fees_usd
FROM ethereum.core.ez_nft_sales
WHERE platform_name = 'nftx'
AND BLOCK_TIMESTAMP > CURRENT_DATE-60
AND event_type = 'redeem'
GROUP BY 1
ORDER BY 3 DESC
),
TOP9_proj AS (
SELECT project_name, rank() OVER (ORDER BY volume_usd DESC) AS vol_rank
FROM NFTX_Proj_Vol
ORDER BY volume_usd DESC
LIMIT 9
),
NFTX_TSer AS (
SELECT date_trunc('day',block_timestamp) AS date, T.project_name, sum(price_usd) AS volume_usd
FROM TOP9_proj AS T LEFT JOIN ethereum.core.ez_nft_sales AS S ON T.project_name = S.project_name
WHERE platform_name = 'nftx'
AND BLOCK_TIMESTAMP > CURRENT_DATE-60
AND event_type = 'redeem'
GROUP BY 1, 2
),
NFTX_TSer2 AS (
SELECT date, project_name, vol_rank, coalesce(volume_usd, 0) AS volume_usd
FROM ((SELECT DISTINCT date FROM NFTX_TSer) CROSS JOIN TOP9_proj) NATURAL LEFT JOIN NFTX_TSer
)
SELECT *
from NFTX_TSer2
Run a query to Download Data