NakedCollector***Most ProfitableCryptoPunks (or Other NFTs)
    Updated 2024-10-21
    WITH sales_data AS (
    SELECT
    s.SELLER_ADDRESS,
    s.TOKENID,
    s.project_name,
    s.BLOCK_TIMESTAMP AS sale_date,
    s.price_usd AS sale_price,
    p.price_usd AS purchase_price,
    s.price_usd - p.price_usd AS profit_loss,
    CASE
    WHEN s.price_usd < p.price_usd THEN 'Loss'
    WHEN s.price_usd > p.price_usd THEN 'Profit'
    ELSE 'Break Even'
    END AS sale_result
    FROM
    ethereum.nft.ez_nft_sales s
    JOIN
    ethereum.nft.ez_nft_sales p
    ON
    s.TOKENID = p.TOKENID
    AND s.SELLER_ADDRESS = p.BUYER_ADDRESS
    WHERE
    --s.project_name = 'CRYPTOPUNKS'
    s.NFT_ADDRESS= '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb'
    AND s.BLOCK_TIMESTAMP >= current_timestamp - interval '90 days'
    AND p.BLOCK_TIMESTAMP < s.BLOCK_TIMESTAMP
    )
    SELECT
    m.TOKENID_NAME,
    project_name,
    sd.TOKENID,

    m.TRAITS,
    COUNT(*) AS total_sales,
    SUM(CASE WHEN sd.sale_result = 'Loss' THEN 1 ELSE 0 END) AS loss_count,
    SUM(CASE WHEN sd.sale_result = 'Profit' THEN 1 ELSE 0 END) AS profit_count,
    QueryRunArchived: QueryRun has been archived