kaibladeMerkle Perp Trades validation
    Updated 2024-08-25
    -- forked from Merkle Perp Trades @ https://flipsidecrypto.xyz/edit/queries/4e1068f6-4bd4-4154-90ee-ad4cb1676319

    WITH perp_trades_raw AS
    (SELECT block_timestamp, block_number,tx_hash, version, event_address, event_index, event_data,
    (CASE
    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
    FROM
    aptos.core.fact_events
    WHERE
    event_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'
    AND event_module = 'trading'
    AND event_resource = 'PositionEvent'
    AND account_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'
    ),

    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
    (SELECT event.block_timestamp, event.block_number, event.version, event.tx_hash, event.event_index,