mz0111flow down bad
Updated 2023-05-05
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
›
⌄
with tab1 as (select
RECORDED_HOUR::date as p_date,
avg (CLOSE) as USDprice
from flow.core.fact_hourly_prices
where TOKEN = 'Flow'
group by 1 )
SELECT
date_trunc('week' , block_timestamp) AS week,
case when NFT_COLLECTION = 'A.0b2a3299cc857e29.TopShot' then 'Top Shot'
when NFT_COLLECTION = 'A.e4cf4bdc1751c65d.AllDay' then 'NFL ALL DAY'
when NFT_COLLECTION = 'A.87ca73a41bb50ad5.Golazos' then 'La Liga Golazos'
when NFT_COLLECTION = 'A.329feb3ab062d289.UFC_NFT' then 'UFC Strike'
END AS Collection_name,
count(distinct tx_id) as TXS,
count(distinct BUYER) as buyers,
count(distinct seller) as sellers,
count(distinct NFT_ID) as NFTs,
sum(case when currency='A.1654653399040a61.FlowToken' then price*USDprice else price end) as USD_Volume,
USD_Volume/ TXS as avg_USD_Volume,
sum(USD_Volume) over (order by week) as cum_vol,
sum(TXS) over (order by week) as cum_txs,
sum(buyers) over (order by week) as cum_buyers,
sum(sellers) over (order by week) as cum_seller,
avg(avg_USD_Volume) over (order by week rows between 7 PRECEDING and current row) as MA_7
from flow.core.ez_nft_sales a join tab1 b
on a.BLOCK_TIMESTAMP:: date = b.p_date
where NFT_COLLECTION in ( 'A.0b2a3299cc857e29.TopShot' , 'A.e4cf4bdc1751c65d.AllDay' , 'A.87ca73a41bb50ad5.Golazos' , 'A.329feb3ab062d289.UFC_NFT')
and TX_SUCCEEDED = 'true'
and week >= current_date - 180
group by 1 , 2
Run a query to Download Data