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;