aj314Average Sales & Transaction Volume
    Updated 2023-12-10
    -- Identify the average daily sales price, sale count, and transfer count for the 'Doodles' project
    -- Create CTE to calculate daily sales metrics
    WITH DailySales AS (
    -- Select the truncated month, average sales price, and sale count
    SELECT
    DATE_TRUNC('month', block_timestamp) AS sales_month,
    ROUND(AVG(price), 2) AS avg_sales_price_eth, --Calculate avg price
    COUNT(tx_hash) AS sale_count
    FROM
    ethereum.nft.ez_nft_sales
    WHERE
    project_name = 'Doodles' --Filter by project_name
    GROUP BY
    sales_month --Group by the sales_month
    ),

    -- Create CTE to calculate transfer counts
    TransferVolume AS (
    -- Select the count of transfers and truncated month
    SELECT
    COUNT(tx_hash) AS transfer_count,
    DATE_TRUNC('month', block_timestamp) AS date
    FROM
    ethereum.nft.ez_nft_transfers
    WHERE
    project_name = 'Doodles' --Filter by project_name
    GROUP BY
    date --Group by the date
    )

    -- Final SELECT statement to combine results from both CTEs
    SELECT
    ds.sales_month,
    ds.avg_sales_price_eth,
    ds.sale_count,
    tv.transfer_count
    Run a query to Download Data