Cluetopzjudicial-turquoise
    Updated 2024-08-22
    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;