ChiefHades
    Updated 2023-01-21
    -- 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