NakedCollectoreager-scarlet
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
›
⌄
WITH sales_data AS (
SELECT
s.SELLER_ADDRESS,
s.TOKENID,
s.BLOCK_TIMESTAMP AS sale_date,
s.price_usd AS sale_price,
p.price_usd AS purchase_price,
s.price_usd - p.price_usd AS profit_loss,
CASE
WHEN s.price_usd < p.price_usd THEN 'Loss'
WHEN s.price_usd > p.price_usd THEN 'Profit'
ELSE 'Break Even'
END AS sale_result
FROM
ethereum.nft.ez_nft_sales s
JOIN
ethereum.nft.ez_nft_sales p
ON
s.TOKENID = p.TOKENID AND s.SELLER_ADDRESS = p.BUYER_ADDRESS
WHERE
s.project_name = 'CRYPTOPUNKS'
AND s.BLOCK_TIMESTAMP >= current_timestamp - interval '90 days'
AND p.BLOCK_TIMESTAMP < s.BLOCK_TIMESTAMP
)
SELECT
m.TRAITS,
COUNT(*) AS total_sales,
SUM(CASE WHEN sd.sale_result = 'Loss' THEN 1 ELSE 0 END) AS loss_count,
SUM(CASE WHEN sd.sale_result = 'Profit' THEN 1 ELSE 0 END) AS profit_count,
AVG(sd.profit_loss) AS avg_profit_loss,
MIN(sd.profit_loss) AS max_loss,
MAX(sd.profit_loss) AS max_profit
FROM
sales_data sd
JOIN
ethereum.nft.dim_nft_collection_metadata m
QueryRunArchived: QueryRun has been archived