ChiefHades
Updated 2023-01-21
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
›
⌄
-- total trades
-- total volume
-- unique buyer/sell ratio
-- unique traders vs total_traders -- only looks at buy side, also the aggegations is not correct,
---need examine unique sellers and buyers as "traders" as one rather than seperately, and aggregate differently,
--- could look at unique_traders vs total traders by top collections (vs bottom collections -- defined as low volume and maybe sale price),
---I could also look at the ratio for all marketplaces over time and hadeswap specifically.
-- Hadeswap total trades
-- hadeswap volume ratio compared to all markeplaces
-- would like to examine total unique sellers and traders as one rather than seperately
-- avg hadeswap mint price vs magic eden
-- total number of minters hadeswap vs Magic Eden
--- https://app.flipsidecrypto.com/dashboard/Fqf8ct
WITH marketplace_label AS (
SELECT
program_id as program_id_label,
marketplace as marketplace_label,
count(distinct tx_id) as tx_count
FROM solana.core.fact_nft_sales
WHERE SUCCEEDED = TRUE
AND block_timestamp > '2022-09-10'
GROUP BY 1,2
)
SELECT
date_trunc('day', block_timestamp) as date,
nvl(marketplace, marketplace_label) as platform,
sum(sales_amount) as trade_volume,
sum(case when platform = 'hadeswap' then sales_amount else null end) as hadeswap_vol,
count(distinct purchaser) as distinct_buyers,
count(distinct seller) as distinct_sellers, -- would like to examine total unique sellers and traders as one rather than seperately
count(purchaser) as buyers_count,
count(case when platform = 'hadeswap' then purchaser else null end) as hadeswap_trades,
count(seller) as sellers_count,
count(distinct tx_id) as sales_count,
distinct_buyers/ buyers_count as buy_ratio
Run a query to Download Data