mmdrezaFLOW - 2.5 - Daily Floor price + users growth
Updated 2022-10-27Copy 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 tab1 as (
select min(block_timestamp::date) as min_date,buyer as new_user from flow.core.ez_nft_sales where nft_collection like '%{{project}}' group by new_user
),
flow_price_tb as (
select
timestamp::date as date,
avg (price_usd) as average_flow_price
from flow.core.fact_prices
where symbol like 'FLOW'
and source like 'coinmarketcap'
group by date
)
select
min_date,
min(case
when currency = 'A.1654653399040a61.FlowToken' then price * fp.average_flow_price
else s.price
end) as floor_price,
avg(case
when currency = 'A.1654653399040a61.FlowToken' then price * fp.average_flow_price
else s.price
end) as avg_price,
max(case
when currency = 'A.1654653399040a61.FlowToken' then price * fp.average_flow_price
else s.price
end) as maximum_price,
count(DISTINCT new_user) as new_users,
sum(new_users) over (order by min_date) as users_growth,
nft_collection
from flow.core.ez_nft_sales s
join tab1 on s.block_timestamp::date = tab1.min_date
left outer join flow_price_tb fp on s.block_timestamp::date = fp.date
where nft_collection like '%{{project}}'
group by min_date,nft_collection
Run a query to Download Data