datavortexTop Traders By Transaction Counts
    Updated 2024-10-07
    WITH BuyerCount AS (
    SELECT
    buyer_address AS "buyer address",
    COUNT(DISTINCT tx_hash) AS "transaction count"
    FROM
    arbitrum.nft.ez_nft_sales
    WHERE
    block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '30 DAY'
    GROUP BY
    buyer_address
    ORDER BY
    "transaction count" DESC
    LIMIT 5
    ),
    SellerCount AS (
    SELECT
    seller_address AS "seller address",
    COUNT(DISTINCT tx_hash) AS "transaction count"
    FROM
    arbitrum.nft.ez_nft_sales
    WHERE
    block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '30 DAY'
    GROUP BY
    seller_address
    ORDER BY
    "transaction count" DESC
    LIMIT 5
    )

    SELECT
    'Top Buyers by Transaction Count' AS category,
    NULL AS "seller address",
    "buyer address",
    CASE
    WHEN "transaction count" >= 1e9 THEN TO_CHAR(ROUND("transaction count" / 1e9, 2)) || 'B'
    WHEN "transaction count" >= 1e6 THEN TO_CHAR(ROUND("transaction count" / 1e6, 2)) || 'M'
    QueryRunArchived: QueryRun has been archived