Ali3NNew vs Existing Traders Over Time (FLOW NFT Tracker)
Updated 2024-07-25
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
›
⌄
with flowpricet as (
select hour::date as day,
avg(price) as flowprice
from flow.price.ez_prices_hourly
where symbol ='FLOW'
group by 1),
maintable as (
select block_timestamp,
tx_id,
buyer,
currency,
price,
row_number () over (partition by buyer order by block_timestamp asc) as rn
from flow.nft.ez_nft_sales
where tx_succeeded ilike 'TRUE'
and substr(nft_collection,20) ilike '%{{NFT_Collection}}%')
select date_trunc ('{{Time_Interval}}',block_timestamp) as date,
case when rn = '1' then 'New Purchasers' else 'Existing Purchasers' end as type,
count (Distinct tx_id) as Sales,
count (distinct buyer) as Buyers,
sum (case when currency in ('A.ead892083b3e2c6c.FlowUtilityToken','A.1654653399040a61.FlowToken') then price*flowprice else price end) as Sales_Volume
from maintable t1 join flowpricet t2 on t1.block_timestamp::Date = t2.day
where block_timestamp >= '{{Start_Date}}' and block_timestamp <= '{{End_Date}}'
group by 1,2
order by 1 desc
Auto-refreshes every 12 hours
QueryRunArchived: QueryRun has been archived