txns as (

tx_hash,

block_timestamp,

nvl(a.decoded_log:_from, a.decoded_log:from) as seller,

nvl(a.decoded_log:_to, a.decoded_log:to) as buyer,

a.contract_address as collection_id,

a.contract_name as collection_name,

nvl(a.decoded_log:_tokenId, a.decoded_log:tokenId) as token_id,

b.amount as price,

b.amount_usd as price_usd

ronin.core.ez_decoded_event_logs a

ronin.core.ez_native_transfers b using (tx_hash, origin_to_address)

origin_to_address = '0x0000000000000068f116a894984e2db1123eb395'

and a.event_name = 'Transfer'

and token_id is not null

and seller = b.to_address

and trace_address != 'ORIGIN'

),

main as (

tx_hash,

block_timestamp,

seller,

buyer,

collection_id,

collection_name,

token_id,

price / (select count(*) from txns b where a.seller = b.seller) as price,

price_usd / (select count(*) from txns b where a.seller = b.seller) as price_usd