SELECT

tx_hash as tx,

event_inputs['_to'] as nft_to_address

FROM polygon.core.fact_event_logs

--WHERE tx_hash LIKE '0x0bdbb1bba045dd648c2e98ce49084b259e7d69679a2f7bd7a93623a9f8c8e0df'

WHERE contract_address LIKE '0x91ac106090fe2b0fa7d01efdf4487a5bfafad7fa'

AND event_name LIKE 'TransferSingle'

), tab2 as (

SELECT

tx_hash,

block_timestamp,

nft_to_address as buyer,

sum(CASE WHEN from_address = nft_to_address THEN raw_amount / power(10, 18) end) as price_eth

FROM polygon.core.fact_token_transfers

LEFT outer JOIN tab1

on tx = tx_hash

WHERE tx_hash in (SELECT tx FROM tab1)

AND contract_address LIKE '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619'

GROUP BY 1,2,3

SELECT

date_trunc('hour', block_timestamp) as day,

count(DISTINCT buyer) as buyers,

count(*) as sales,

sum(price_eth) as sales_volume,

median(price_eth) as medain_price,

max(price_eth) as max_price

FROM tab2

GROUP BY 1