adriaparcerisasflowverse secondary sales 2
Updated 2024-09-11
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
prices as (
SELECT
trunc(hour,'day') as day,
avg(price) as price_usd
from flow.price.ez_prices_hourly
where symbol ilike '%Flow%'
--and recorded_hour>'2023-10-01'
group by 1
),
sales as (
SELECT
distinct tx_id, trunc(block_timestamp,'month') as time, count(distinct event_data:nftID) as nft_id,
case when event_data:salePaymentVaultType ilike '%flowtoken%' then 'FLOW' else 'USDC' end as pay_method,
round(event_data:salePrice,2) as sale_price,
sale_price*0.05 as royalty_fee, sale_price*0.01 as marketplace_fee
from flow.core.fact_events x
--join prices y on trunc(block_timestamp,'hour')=hour
where event_contract='A.4eb8a10cb9f87357.NFTStorefrontV2'
and event_type='ListingCompleted' and event_data:customID='flowverse-nft-marketplace'
and event_data:purchased='true'
group by 1,2,4,5,6,7
) --select * from sales
SELECT
time as date,
COUNT(DISTINCT tx_id) AS sales,
sum(sales) over (order by date) as cum_sales,
SUM(COALESCE(sale_price, 0)) AS volume_usd,
sum(volume_usd) over (order by date) as cum_volume_usd,
avg(COALESCE(sale_price, 0)) AS avg_nft_price,
min(COALESCE(sale_price, 0)) AS min_nft_price,
max(COALESCE(sale_price, 0)) AS max_nft_price,
SUM(COALESCE(royalty_fee, 0)) AS total_generated_royalty_fees,
sum(total_generated_royalty_fees) over (order by date) as cum_total_generated_royalty_fees,
SUM(COALESCE(marketplace_fee, 0)) AS total_generated_marketplace_fees,
QueryRunArchived: QueryRun has been archived