kaibladeMerkle Perp Trades validation
Updated 2024-08-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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,