gigiokobaflying-cyan
    Updated 2025-02-23
    WITH nft_transfers AS (
    SELECT
    ft.block_timestamp,
    ft.tx_hash,
    ft.from_address,
    ft.to_address,
    ft.value as payment_amount,
    el.contract_address as nft_contract,
    dc.name as collection_name
    FROM monad.testnet.fact_transactions ft
    JOIN monad.testnet.fact_event_logs el
    ON ft.tx_hash = el.tx_hash
    LEFT JOIN monad.testnet.dim_contracts dc
    ON el.contract_address = dc.address
    WHERE el.topic_0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer event signature
    AND ft.block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP())
    AND ft.tx_succeeded = true
    AND ft.value > 0 -- Only include transfers with payment
    )

    SELECT
    collection_name,
    nft_contract,
    COUNT(*) as number_of_sales,
    SUM(payment_amount) as total_volume,
    AVG(payment_amount) as avg_sale_price
    FROM nft_transfers
    GROUP BY 1, 2
    ORDER BY total_volume DESC;