DiamondWeekly Comparison copy
Updated 2024-03-15
999
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
›
⌄
-- forked from Masi / Weekly Comparison @ https://flipsidecrypto.xyz/Masi/q/V2ickNzI8HEv/weekly-comparison
with tb1 as ( select trunc(RECORDED_HOUR,'day') as day,
avg(open) as avg_price
from flow.price.fact_hourly_prices
where token = 'Flow'
group by 1
)
,
tb2 as ( select tx_id,
trunc(block_timestamp,'day') as day,
marketplace,
case when marketplace = 'A.85b075e08d13f697.OlympicPinMarket' then 'Olympic Pin'
when marketplace = 'A.921ea449dffec68a.FlovatarMarketplace' then 'Flovatar'
when marketplace = 'A.a49cc0ee46c54bfb.MotoGPNFTStorefront' then 'MotoGP'
when marketplace = 'A.097bafa4e0b48eef.FindMarketSale' then 'Find Market'
when marketplace = 'A.62b3063fbe672fc8.ZeedzMarketplace' then 'Zeedz'
when marketplace = 'A.64f83c60989ce555.ChainmonstersMarketplace' then 'Chainmonsters' end as market_name,
buyer,
currency,
case when currency ilike '%flow%' then price*avg_price else price end as volume
from flow.nft.ez_nft_sales a join tb1 b on a.block_timestamp::date = b.day
where TX_SUCCEEDED = 'true'
and marketplace in ('A.85b075e08d13f697.OlympicPinMarket','A.a49cc0ee46c54bfb.MotoGPNFTStorefront',
'A.921ea449dffec68a.FlovatarMarketplace','A.62b3063fbe672fc8.ZeedzMarketplace',
'A.097bafa4e0b48eef.FindMarketSale','A.64f83c60989ce555.ChainmonstersMarketplace'))
,
-- Credited to Adria
tb3 as ( select DISTINCT tx_id as hash,
block_timestamp
from flow.core.fact_events
where event_type = 'ListingCompleted'
and event_data:customID = 'flowverse-nft-marketplace'
),
tb4 as (select DISTINCT tx_id
QueryRunArchived: QueryRun has been archived