ddccOpensea Hot or Not USD Volume
Updated 2021-10-06
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
›
⌄
--- sale counts: gains and losses comparing this week vs previous week
WITH project_names AS
(SELECT contract_address,
lower(project_name) AS project_name
FROM ethereum.nft_events
WHERE project_name IS NOT NULL
GROUP BY 1,
2),
T3 AS
(SELECT contract_address,
sum(price) AS eth_sales_sum_t3,
min(price) AS eth_sales_min_t3,
avg(price) AS eth_sales_mean_t3,
count(tx_id) AS eth_sales_count_t3
FROM ethereum.nft_events
WHERE event_platform = 'opensea'
AND (tx_currency = 'ETH'
OR tx_currency = 'WETH')
AND price > 0
AND event_type = 'sale'
AND block_timestamp >= getdate() - interval '28 days'
AND block_timestamp < getdate() - interval '21 days'
GROUP BY contract_address),
T2 AS
(SELECT contract_address,
sum(price) AS eth_sales_sum_t2,
min(price) AS eth_sales_min_t2,
avg(price) AS eth_sales_mean_t2,
count(tx_id) AS eth_sales_count_t2
FROM ethereum.nft_events
WHERE event_platform = 'opensea'
AND (tx_currency = 'ETH'
OR tx_currency = 'WETH')
AND price > 0