WITH perp_trades_raw AS

(SELECT block_timestamp, block_number,tx_hash, version, event_address, event_index, event_data,

WHEN event_data:event_type::INTEGER = 0 THEN 'Open Position'

WHEN event_data:event_type::INTEGER = 1 THEN 'Update Position'

WHEN event_data:event_type::INTEGER = 2 THEN 'Close Position'

WHEN event_data:event_type::INTEGER = 3 THEN 'Liquidate Close Position'

WHEN event_data:event_type::INTEGER = 4 THEN 'Take Profit Close Position'

WHEN event_data:event_type::INTEGER = 5 THEN 'Stop Loss Close Position'

END) AS event_type,

fact_events_id AS ez_perp_trades_id,

CURRENT_TIMESTAMP AS inserted_timestamp,

CURRENT_TIMESTAMP AS modified_timestamp

aptos.core.fact_events

event_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'

AND event_module = 'trading'

AND event_resource = 'PositionEvent'

AND account_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'

AND event_data:user::VARCHAR = '0xce902a27ef658e11ee9744a4d5be6046dab00b28b5cc9d6afedb6cdc7432defe' -- Merkle Top Trader

),

pair_info AS

(SELECT tx_hash,

REPLACE(SPLIT(SPLIT(TRIM(change_resource, 'PairState<>'), ',')[0],'::')[2],'_', '/') AS trading_pair,

REPLACE(SPLIT(SPLIT(TRIM(change_resource, 'PairState<>'), ',')[1],'::')[2],'_', '/') AS collateral_asset

FROM aptos.core.fact_changes

WHERE change_resource ILIKE 'PairState<%'

AND change_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'

AND change_module = 'trading'

),

merkle_perp_trades AS