SELECT date_trunc('day',HOUR) as date ,

avg(price) as ETH_price

FROM ethereum.core.fact_hourly_token_prices

WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

GROUP BY 1

),

all_contract as (

SELECT DISTINCT CONTRACT_ADDRESS

FROM polygon.core.FACT_EVENT_LOGS

WHERE ORIGIN_FROM_ADDRESS = '0x36fb3886cf3fc4e44d8b99d9a8520425239618c2'

and ORIGIN_FUNCTION_SIGNATURE = '0x60806040'

and EVENT_NAME = 'OwnershipTransferred'

and TX_STATUS = 'SUCCESS'

),

all_tx AS (

SELECT tx_hash ,

a.contract_address ,

event_inputs:_to AS buyer,

event_inputs:_from AS seller,

event_inputs:_id AS tokenID

from polygon.core.FACT_EVENT_LOGS a

LEFT JOIN all_contract b

ON a.contract_address = b.contract_address

where ORIGIN_TO_ADDRESS = '0x00000000006c3852cbef3e08e8df289169ede581' -- Seaport 1.1

AND a.contract_address IN (SELECT contract_address FROM all_contract )

AND EVENT_NAME = 'TransferSingle'

),

volume_add AS (

SELECT a.block_timestamp::date as date , a.tx_hash , b.contract_address AS NFT_contract , b.buyer , b.seller , b.tokenID , a.event_inputs:value/pow (10,18) as amount_ETH , amount_ETH*ETH_price AS amount_USD

from polygon.core.FACT_EVENT_LOGS a

LEFT JOIN all_tx b

ON a.tx_hash = b.tx_hash

JOIN ETH_p c