Alexaycollections
Updated 2022-10-24Copy Reference Fork
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 flow_price as (select TIMESTAMP::date as p_date, avg (price_usd) as flow_price
from flow.core.fact_prices
where symbol ='FLOW' and source ='coinmarketcap'
group by 1),
nft_price as ( SELECT*, price * b.flow_price as nft_usd_price
from flow.core.ez_nft_sales a join flow_price b on a.block_timestamp::date = b.p_date ),
raceday_buyer as (select block_timestamp, tx_id, buyer, nft_collection, nft_usd_price
from nft_price
where nft_collection = 'A.329feb3ab062d289.RaceDay_NFT'
and tx_succeeded = 'TRUE'),
other_collec_buyer as ( select date_trunc('week', block_timestamp) as date,
split(nft_collection, '.')[2] as collection,
COUNT(DISTINCT tx_id) sales_Cnt,
COUNT(DISTINCT buyer) buyer_Cnt,
sum(nft_usd_price) as sales_usd_vol,
row_number() over (partition by sales_Cnt order by date DESC) as rank
from nft_price a JOIN raceday_buyer b USING(buyer)
where nft_collection != 'A.329feb3ab062d289.RaceDay_NFT'
and tx_succeeded = 'TRUE'
and a.block_timestamp > b.block_timestamp
group by 1,2
qualify rank <= 10)
SELECT date_trunc('week', block_timestamp) as date,
split(nft_collection, '.')[2] as collection,
COUNT(DISTINCT tx_id) sales_Cnt,
COUNT(DISTINCT buyer) buyer_Cnt,
sum(nft_usd_price) as sales_usd_vol,
row_number() over (order by date DESC) as rank
FROM raceday_buyer
group by 1, 2
UNION
Run a query to Download Data