Query editor
with liq_evts as (select*fromerc20_optimism.evt_Transferwherecontract_address = lower('0x8c6f28f2F1A3C87F0f938b96d27520d9751ec8d9')and to = lower('0xfeEFEEfeefEeFeefEEFEEfEeFeefEEFeeFEEFEeF')and evt_block_time > '2022-12-19'::timestamp), liquidation_pre as (selectcoalesce(UPPER(trim('\u0000' from (unhex(substring(ft.trackingCode,3))))), NULL) as tracking_code,coalesce(UPPER(trim('\u0000' from (unhex(substring(ft.marketKey,3))))), NULL) as marketKey,coalesce(UPPER(trim('\u0000' from unhex(substring(ft.baseAsset, 3)))), NULL) as PERP,pm.contract_address, pm.evt_tx_hash, pm.evt_block_time, pm.account, (pm.fee + (lq.value::float))/1e18 as fee, pm.price/1e18 as lastPrice,0 as margin, 0 as size, pm.size/1e18 * -1 as tradeSize,1 as helperfromsynthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PositionLiquidated pmleft join synthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PerpsTracking ft on pm.evt_tx_hash=ft.evt_tx_hash and pm.fee=ft.feeinner join liq_evts lq on pm.evt_tx_hash = lq.evt_tx_hash and pm.evt_index = (lq.evt_index - 1)),perps_table as (selectcoalesce(UPPER(trim('\u0000' from (unhex(substring(marketKey,3))))), NULL) as marketKey,coalesce(UPPER(trim('\u0000' from unhex(substring(baseAsset, 3)))), NULL) as PERP, contract_addressfrom synthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PerpsTrackinggroup by marketKey, PERP, contract_address),liquidation_table as (select NULL as tracking_code, t2.marketKey, t2.PERP, t1.contract_address,t1.evt_tx_hash, t1.evt_block_time, t1.account, t1.fee, t1.lastPrice,t1.margin, t1.size, t1.tradeSize,t1.helperfrom liquidation_pre t1left join perps_table t2 on t1.contract_address = t2.contract_address),perps_tx as (selectcoalesce(UPPER(trim('\u0000' from (unhex(substring(ft.trackingCode,3))))), 'unknown') as tracking_code,coalesce(UPPER(trim('\u0000' from (unhex(substring(ft.marketKey,3))))), 'unknown') as marketKey,coalesce(UPPER(trim('\u0000' from unhex(substring(ft.baseAsset, 3)))),'unknown') as PERP,pm.contract_address, pm.evt_tx_hash, pm.evt_block_time, pm.account, pm.fee/1e18 as fee, pm.lastPrice/1e18 as lastPrice,pm.margin/1e18 as margin, pm.size/1e18 as size, pm.tradeSize/1e18 as tradeSize,case when pm.size = 0 then 1 else 0 end as helperfromsynthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PositionModified pmleft join synthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PerpsTracking ft on pm.evt_tx_hash=ft.evt_tx_hash and pm.fee=ft.feewhere ft.evt_tx_hash is not null),trades as (