Hemin1.2 Daily Sales
Updated 2022-12-03Copy 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_usd as (
select
avg(PRICE_USD) as usd , TIMESTAMP::date as day
from flow.core.fact_prices
where day >= '2022-01-01' and
TOKEN_CONTRACT = 'A.1654653399040a61.FlowToken'
group by day
)
, nft_sales as (
select a.* ,
iff( BLOCK_TIMESTAMP::date > '2022-11-23', 'After ThanksGiving', 'Before ThanksGiving') as time,
iff (CURRENCY = 'A.1654653399040a61.FlowToken', b.usd *PRICE ,PRICE) as usd
from flow.core.ez_nft_sales a LEFT JOIN flow_usd b on BLOCK_TIMESTAMP::date = b.day
where BLOCK_TIMESTAMP > '2022-02-01'
and nft_collection = 'A.e4cf4bdc1751c65d.AllDay'
and TX_SUCCEEDED = TRUE
), new_buyer as (select count(BUYER) as new_user , first_buy from (select min(BLOCK_TIMESTAMP)::date as first_buy ,BUYER from flow.core.ez_nft_sales
where BLOCK_TIMESTAMP >= '2022-01-01' and nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and TX_SUCCEEDED = TRUE GROUP by BUYER ) GROUP by first_buy )
, new_seller as (select count(SELLER) as new_seller , first_sell from (select min(BLOCK_TIMESTAMP)::date as first_sell ,SELLER from flow.core.ez_nft_sales
where BLOCK_TIMESTAMP >= '2022-01-01' and nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and TX_SUCCEEDED = TRUE GROUP by SELLER ) GROUP by first_sell )
select
-- date_trunc('week',BLOCK_TIMESTAMP) as week ,
BLOCK_TIMESTAMP::date as date_time ,
time,
count(DISTINCT SELLER) as uniqe_seller ,
count(DISTINCT BUYER) as uniqe_BUYER ,
count(TX_ID) as num_tx ,
count(DISTINCT NFT_ID) as num_nft,
min(new_user) as num_new_buyer ,
min(new_seller) as num_new_sellers ,
sum(usd) as usd_volume,
avg(usd) as avg_usd_sales
,avg(usd_volume) over (order by date_time rows between 7 PRECEDING and current row) as "7-day average Moving Total USD"
,avg(avg_usd_sales) over (order by date_time rows between 7 PRECEDING and current row) as "7-day Moving average USD"
Run a query to Download Data