Cluetopzjudicial-turquoise
Updated 2024-08-22
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
›
⌄
WITH Lp AS (
SELECT
date_trunc('month', ) AS date,
AVG(CLOSE) AS price
FROM
solana.price.fact_prices_ohlc_hourly
JOIN
solana.price.dim_asset_metadata
ON solana.price.fact_prices_ohlc_hourly.asset_id = solana.price.dim_asset_metadata.asset_id
WHERE SYMBOL = 'SOL'
GROUP BY 1
),
LP AS (
SELECT
COUNT(DISTINCT SELLER) AS seller_count,
COUNT(DISTINCT tx_id) AS sale,
COUNT(DISTINCT purchaser) AS buyer_count,
COUNT(DISTINCT purchaser) + COUNT(DISTINCT seller) AS total_unique_users, -- Total unique users
SUM(SALES_AMOUNT * price) AS volume_usd,
AVG(SALES_AMOUNT * price) AS average_volume
FROM
solana.nft.fact_nft_sales
LEFT OUTER JOIN Lp
ON date_trunc('day', block_timestamp) = date
WHERE
marketplace = 'hyperspace'
AND block_timestamp >= DATEADD(month, -6, CURRENT_DATE())
)
SELECT
'hyperspace' AS marketplace,
*
FROM LP;