0xHaM-ddaily
Updated 2023-02-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 priceTb 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 priceTb b on a.block_timestamp::date = b.p_date
where nft_collection = 'A.329feb3ab062d289.RaceDay_NFT'
and tx_succeeded = 'TRUE'
)
select
BLOCK_TIMESTAMP::DATE as DATE,
count(distinct tx_id) as Sales_Cnt,
count(distinct buyer) as Buyers_Cnt,
count(distinct seller) as Sellers_Cnt,
count(distinct nft_collection) as Collections_Cnt,
count(distinct marketplace) as Marketplaces_Cnt,
count(distinct nft_id) as Tokens_Cnt,
sum(price) as Total_flow_Vol,
sum(nft_usd_price) as Total_USD_Vol,
avg(nft_usd_price) as Avg_USD_Amt,
avg(price) as Avg_flow_Vol,
avg(nft_usd_price) as Avg_USD_Vol,
sum(Sales_Cnt) over (order by date) as cum_Sales_Cnt,
sum(Total_flow_Vol) over (order by date) as cum_Total_flow_Vol,
sum(Total_USD_Vol) over (order by date) as cum_Total_USD_Vol,
avg(Sales_Cnt) over (order by date) as Avg_Sales_Cnt,
avg(Buyers_Cnt) over (order by date) as Avg_Buyers_Cnt,
avg(Avg_USD_Amt) over (order by date rows between 7 PRECEDING and current row) as MA_7,
avg(Avg_USD_Amt) over (order by date rows between 50 PRECEDING and current row) as MA_50
from nft_price
Run a query to Download Data