with main2 AS (

select

m.block_timestamp,

m.tx_id,

pos_id,

Trader,

avg(Volume) AS "Trade Volume",

avg(pnl_usd) AS pnl_usd

from

sei.core.fact_msg_attributes m

inner join (

select

TX_ID,

MSG_TYPE,

ATTRIBUTE_VALUE AS Trader

from

sei.core.fact_msg_attributes

where

MSG_TYPE like 'wasm-position-close%'

and ATTRIBUTE_KEY = 'pos-owner'

) F on F.TX_ID = m.TX_ID

and F.MSG_TYPE = m.MSG_Type

inner join (

select

TX_ID,

MSG_TYPE,

ATTRIBUTE_VALUE AS Volume

from

sei.core.fact_msg_attributes

where

MSG_TYPE like 'wasm-history-trade-volume%'

and ATTRIBUTE_KEY = 'volume-usd'

) N on N.TX_ID = m.TX_ID

and substr(N.MSG_TYPE,27)=substr(m.MSG_TYPE,21)

inner join (