Updated 2024-03-07
    WITH
    ranked_buyers AS (
    SELECT
    buyer_address,
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,
    ROW_NUMBER() OVER (
    PARTITION BY
    buyer_address
    ORDER BY
    BLOCK_TIMESTAMP
    ) AS rn
    FROM
    ethereum.nft.ez_nft_sales
    WHERE
    event_type = 'sale'
    )
    SELECT
    DATE_TRUNC('day', e.BLOCK_TIMESTAMP) AS date,
    SUM(e.price_usd) AS price,
    COUNT(DISTINCT e.TX_HASH) AS volume,
    e.platform_name,
    COUNT(DISTINCT e.buyer_address) AS unique_daily_buyer,
    COUNT(DISTINCT e.seller_address) AS unique_daily_seller,
    COUNT(DISTINCT e.project_name) AS unique_projects_sold,
    COUNT(DISTINCT rb.buyer_address) AS net_new_daily_buyer
    FROM
    ethereum.nft.ez_nft_sales e
    LEFT JOIN ranked_buyers rb ON e.buyer_address = rb.buyer_address
    AND DATE_TRUNC('day', e.BLOCK_TIMESTAMP) = rb.date
    AND rb.rn = 1
    WHERE
    e.event_type = 'sale'
    GROUP BY
    DATE_TRUNC('day', e.BLOCK_TIMESTAMP),
    e.platform_name
    ORDER BY
    QueryRunArchived: QueryRun has been archived