SELECT

block_timestamp,

tx_hash,

decoded_log:newFulfilledAmount::int AS quantity,

(decoded_log:order:value::int / pow(10,18)) * decoded_log:newFulfilledAmount::int AS total_sales_volume, -- price * quantity for total sales volume

decoded_log:offer:collection::string AS collection

FROM ronin.core.ez_decoded_event_logs

WHERE contract_address = lower('0x3ef234bc2a04d86f6041e419458d9acbd077f2c1')

AND topic_0 = '0xad64053f1eeea4079b4aafaca8934fffd9a6304ccdfe094404115202bda3ed03'

AND decoded_log:offer:collection::string = '0x727b7ff568e7173134eb02517c4a87eac390a77b'

),

marketgatewayproxy AS (

SELECT

block_timestamp,

tx_hash,

decoded_log:acceptedSettlePrice::int / pow(10,18) AS price,

decoded_log:matcher::string AS seller,

decoded_log:order[0].extraData[0][2]::int AS tokenid,

decoded_log:order[0].extraData[0][1]::string AS collection,

-- Ensure the price is multiplied by quantity (quantity is inferred from context here)

(decoded_log:acceptedSettlePrice::int / pow(10,18)) * decoded_log:order[0].extraData[0][2]::int AS total_sales_volume

FROM ronin.core.ez_decoded_event_logs

WHERE contract_address = LOWER('0x3b3adf1422f84254b7fbb0e7ca62bd0865133fe3')

AND topic_0 = '0x968d1942d9971cb9c45c722957d854c38f327206399d12ae49ca2f9c5dd06fda'

AND decoded_log:order[0].extraData[0][1]::string = '0x727b7ff568e7173134eb02517c4a87eac390a77b'

),

marketgatewaymultisendproxy AS (

SELECT

block_timestamp,

tx_hash,

topics,

decoded_log,

regexp_substr_all(SUBSTR(DATA, 3), '.{64}') AS segment,