WITH mint_tx as (

SELECT

block_timestamp,

tx_hash,

EVENT_INPUTS,

CONTRACT_ADDRESS,

EVENT_INPUTS:"_id"::string as token_Id,

EVENT_INPUTS:"_to"::string as to_address,

ROW_NUMBER() OVER (PARTITION BY to_address ORDER BY block_timestamp ASC) as rank

FROM polygon.core.FACT_EVENT_LOGS

WHERE 1=1

and event_name = 'TransferSingle'

and ORIGIN_FUNCTION_SIGNATURE IN ('0x669f5a51', '0x5a86c41a')

and EVENT_INPUTS:"_from" = '0x0000000000000000000000000000000000000000'

and TX_STATUS = 'SUCCESS'

QUALIFY rank = 1 -- Only look at the first mint tx, ignore 2nd onwards

, sale_tx as (

SELECT

block_timestamp,

'Reddit' as collection,

tx_hash,

CONTRACT_ADDRESS,

EVENT_INPUTS:"_from" as Seller,

EVENT_INPUTS:"_to" as Buyer,

EVENT_INPUTS:"_id" as Token_ID

FROM polygon.core.FACT_EVENT_LOGS

WHERE CONTRACT_ADDRESS IN (SELECT CONTRACT_ADDRESS FROM mint_tx)

and event_name = 'TransferSingle'

and EVENT_INPUTS:"_from" != '0x0000000000000000000000000000000000000000'

and EVENT_INPUTS:"_to" != '0x0000000000000000000000000000000000000000'

and TX_STATUS = 'SUCCESS'

UNION