with limit_order_tb as (

BLOCK_TIMESTAMP,

TX_HASH,

EVENT_DATA,

EVENT_DATA:user as user,

EVENT_DATA:collateral_delta/1e6 as collateral_delta,

EVENT_DATA:long_open_interest/1e6 as long_open_interest,

EVENT_DATA:short_open_interest/1e6 as short_open_interest,

EVENT_DATA:price/1e10 as price,

EVENT_DATA:pnl_without_fee/1e6 as pnl_without_fee,

EVENT_DATA:size_delta/1e6 as size_delta,

round(size_delta/collateral_delta) as leverage,

split(PAYLOAD:type_arguments[0], '::')[2] as pair_types,

EVENT_DATA:is_increase::Boolean as is_increase,

EVENT_DATA:is_long::Boolean as is_long,

EVENT_DATA:is_partial::Boolean as is_partial,

EVENT_DATA:is_profit::Boolean as is_profit

FROM aptos.core.fact_events

join aptos.core.fact_transactions using(BLOCK_TIMESTAMP, TX_HASH)

WHERE EVENT_ADDRESS = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'

AND EVENT_MODULE = 'trading'

AND EVENT_RESOURCE in ('PositionEvent')

HAVING pnl_without_fee !=0

),

pnls as (SELECT

BLOCK_TIMESTAMP::date as date,

CASE

WHEN pair_types LIKE '%XAU%' THEN 'Commodities'

WHEN pair_types LIKE '%XAG%' THEN 'Commodities'

WHEN pair_types LIKE '%EUR%' THEN 'Forex'

WHEN pair_types LIKE '%USD_JPY%' THEN 'Forex'

WHEN pair_types LIKE '%GBP%' THEN 'Forex'