andre4top trader joe nft stats by top 10 collections
    Updated 2024-09-30

    WITH top_projects AS (
    SELECT
    project_name
    FROM
    avalanche.nft.ez_nft_sales
    WHERE
    platform_name = 'joepegs' and block_timestamp>=current_date-INTERVAL '1 WEEK'
    GROUP BY
    project_name
    ORDER BY
    SUM(price_usd) DESC
    LIMIT 10
    ),
    joe as (
    SELECT
    project_name,
    buyer_address,
    tx_hash,
    price_usd AS volume
    FROM
    avalanche.nft.ez_nft_sales
    WHERE
    platform_name = 'joepegs'
    AND project_name IN (SELECT project_name FROM top_projects)
    AND block_timestamp > current_date - interval '1 week'
    ),
    alls AS (
    SELECT
    project_name,
    COUNT(DISTINCT tx_hash) AS sales,
    COUNT(DISTINCT buyer_address) AS active_users,
    SUM(volume) AS volume,
    AVG(volume) AS avg_price
    FROM joe
    GROUP BY 1
    QueryRunArchived: QueryRun has been archived