Ez Nft Sales

-- Daily NFT market volume by platform
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform_name,
    COUNT(*) as sales_count,
    COUNT(DISTINCT tx_hash) AS unique_sales_transaction_count,
    COUNT(DISTINCT buyer_address) AS unique_buyers,
    COUNT(DISTINCT contract_address) AS collections_traded,
    SUM(price_usd) AS total_volume_usd,
    AVG(price_usd) AS avg_sale_price_usd
FROM <blockchain_name>.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 30
    AND price_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 7 DESC;

-- Top selling NFT collections
SELECT 
    contract_address,
    name AS collection_name,
    COUNT(*) AS sales_count,
    COUNT(DISTINCT token_id) AS unique_tokens_sold,
    COUNT(DISTINCT buyer_address) AS unique_buyers,
    SUM(price_usd) AS total_volume_usd,
    AVG(price_usd) AS avg_price_usd,
    MAX(price_usd) AS highest_sale_usd
FROM <blockchain_name>.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 7
    AND price_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 6 DESC
LIMIT 50;

-- Marketplace competition analysis
WITH platform_metrics AS (
    SELECT 
        platform_name,
        COUNT(*) AS total_sales,
        SUM(price_usd) AS total_volume_usd,
        COUNT(DISTINCT buyer_address) AS unique_buyers,
        COUNT(DISTINCT seller_address) AS unique_sellers,
        AVG(platform_fee / NULLIF(price, 0) * 100) AS avg_platform_fee_pct,
        SUM(platform_fee_usd) AS total_platform_revenue_usd
    FROM <blockchain_name>.nft.ez_nft_sales
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND price_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    platform_name,
    total_sales,
    total_volume_usd,
    total_volume_usd * 100.0 / SUM(total_volume_usd) OVER () AS market_share_pct,
    unique_buyers,
    unique_sellers,
    avg_platform_fee_pct,
    total_platform_revenue_usd
FROM platform_metrics
ORDER BY total_volume_usd DESC;

-- Whale activity tracking
WITH buyer_stats AS (
    SELECT 
        buyer_address,
        COUNT(*) AS purchases,
        COUNT(DISTINCT contract_address) AS unique_collections,
        SUM(price_usd) AS total_spent_usd,
        AVG(price_usd) AS avg_purchase_price,
        MAX(price_usd) AS highest_purchase
    FROM <blockchain_name>.nft.ez_nft_sales
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND price_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    CASE 
        WHEN total_spent_usd < 1000 THEN '< $1K'
        WHEN total_spent_usd < 10000 THEN '$1K - $10K'
        WHEN total_spent_usd < 100000 THEN '$10K - $100K'
        WHEN total_spent_usd < 1000000 THEN '$100K - $1M'
        ELSE '> $1M'
    END AS buyer_tier,
    COUNT(*) AS buyer_count,
    SUM(purchases) AS total_purchases,
    AVG(unique_collections) AS avg_collections_per_buyer,
    SUM(total_spent_usd) AS tier_total_spent
FROM buyer_stats
GROUP BY 1
ORDER BY MIN(total_spent_usd);

-- Creator royalty analysis
SELECT 
    name AS collection_name,
    contract_address,
    COUNT(*) AS sales_with_royalties,
    SUM(creator_fee) AS total_creator_fees,
    SUM(creator_fee_usd) AS total_creator_fees_usd,
    AVG(creator_fee / NULLIF(price, 0) * 100) AS avg_royalty_pct,
    SUM(creator_fee_usd) / NULLIF(SUM(price_usd), 0) * 100 AS effective_royalty_rate
FROM <blockchain_name>.nft.ez_nft_sales
WHERE creator_fee > 0
    AND price > 0
    AND block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
HAVING COUNT(*) > 10
ORDER BY total_creator_fees_usd DESC
LIMIT 100;