fb3ce38c-7bfe-475c-bca8-3f1e44ae80a6full_stats_eth_mp-s_monthly
    Updated 2023-07-05
    lllllllllllllllllllll-- forked from full_stats_eth_mp-s @ https://flipsidecrypto.xyz/edit/queries/7b95d25f-8521-4303-a0bb-aa3921689db3

    WITH ranked_buyers AS (
    SELECT
    buyer_address,
    DATE_TRUNC('month',BLOCK_TIMESTAMP) AS date,
    ROW_NUMBER() OVER (PARTITION BY buyer_address ORDER BY BLOCK_TIMESTAMP) AS rn
    FROM
    ethereum.core.ez_nft_sales
    WHERE
    event_type ='sale'
    )

    SELECT
    DATE_TRUNC('month',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.core.ez_nft_sales e
    LEFT JOIN
    ranked_buyers rb ON e.buyer_address = rb.buyer_address AND DATE_TRUNC('month', e.BLOCK_TIMESTAMP) = rb.date AND rb.rn = 1
    WHERE
    e.event_type ='sale'
    GROUP BY
    DATE_TRUNC('month', e.BLOCK_TIMESTAMP),
    e.platform_name
    ORDER BY
    DATE_TRUNC('month', e.BLOCK_TIMESTAMP) DESC


    Run a query to Download Data