select tx_hash, origin_to_address as notSeller, contract_address as NFT_project, origin_from_address as Buyer, event_inputs:tokenId as nft_id

from avalanche.core.fact_event_logs

where --tx_hash='0xeaaf835fd44247a2e316bfd69378ca3be79ff601a7d8d4142b7c8942becd255f' and

event_inputs:from=origin_to_address and event_inputs:to=origin_from_address and event_inputs:tokenId is not null

and event_name='Transfer' and tx_status='SUCCESS'),

xs as (

select tx_hash, event_inputs:owner as owner

from avalanche.core.fact_event_logs

where event_inputs:approved='0x0000000000000000000000000000000000000000'),

d1 as

(select xb.tx_hash, NFT_project, project_name, Buyer, owner, nft_id

from xb

inner join xs

on xb.tx_hash=xs.tx_hash

left join avalanche.core.dim_labels l

on xb.nft_project=l.address),

d2 as (

select tx_hash,block_timestamp as t,eth_from_address as payer,ETH_to_address as Seller, amount

from avalanche.core.ez_avax_transfers

-- where tx_hash = '0xeaaf835fd44247a2e316bfd69378ca3be79ff601a7d8d4142b7c8942becd255f'

),

rawsales as

(SELECT d2.tx_hash, t , NFT_project, project_name, Buyer, owner, nft_id, amount

FROM d1

INNER JOIN d2

ON d1.tx_hash=d2.tx_hash and d1.Buyer=d2.payer and d1.owner=d2.seller

order by 3)

SELECT owner, count(nft_id) as n_nft_sold