WITH runiverse_nft_transfers AS (

-- All Runiverse NFT transfers (direct transfers and marketplace)

SELECT

tx_hash,

block_timestamp,

from_address,

to_address,

token_id,

quantity,

name AS nft_name

FROM ronin.nft.ez_nft_transfers

WHERE name ILIKE '%runiverse%'

AND contract_address IN ('0x578b65568b7a6a081a477748590774a64d8a4923' -- RuniverseItem

,'0x775f0a0bb8258501d0862df38a7f7ad8f8f7423d' -- RuniverseLand

)

AND to_address != '0x0000000000000000000000000000000000000000' -- Exclude burns

),

marketplace_transactions AS (

-- Identify marketplace transactions with payment information

-- The filter and addresses for marketplace transactions was forked from Mrfti / scared-magenta copy @ https://flipsidecrypto.xyz/Mrfti/q/2qli-28Wm1Jz/scared-magenta-copy

SELECT

logs.tx_hash,

pays.from_address AS buyer,

nfts.from_address AS seller,

pays.amount_usd AS transaction_value

FROM ronin.core.fact_event_logs logs

INNER JOIN (

SELECT tx_hash, from_address

FROM runiverse_nft_transfers

) nfts ON logs.tx_hash = nfts.tx_hash

LEFT JOIN (

SELECT

tx_hash,

from_address,