-- forked from Levana iiquidation @ https://flipsidecrypto.xyz/edit/queries/4e3d8dad-680d-47c7-af9a-cc79781a698b

-- forked from Levana pnl @ https://flipsidecrypto.xyz/edit/queries/5cefc3f9-ca17-4f4e-9395-25d6e1375740

with main2 AS (

select

m.block_timestamp,

m.tx_id,

pos_id,

CASE

when ATTRIBUTE_VALUE='osmo1nzddhaf086r0rv0gmrepn3ryxsu9qqrh7zmvcexqtfmxqgj0hhps4hruzu' THEN 'BTC'

when ATTRIBUTE_VALUE='osmo1hd7r733w49wrqnxx3daz4gy7kvdhgwsjwn28wj7msjfk4tde89aqjqhu8x' THEN 'ATOM'

when ATTRIBUTE_VALUE='osmo127aqy4697zqn27z0vqr3x2n8lraf27t7udvl6ef5hcwmwhjadegq9vytdj' THEN 'OSMO'

when ATTRIBUTE_VALUE='osmo19c7hdlfvu7cddr0smfz9luaj8375qhfr3s0gtsk087laqfzxlu3qsnk47e' THEN 'axlETH'

when ATTRIBUTE_VALUE='osmo186nlf2fwfglq8u4nj3f7mwg8uc79j22qhaau4scdyur47e0fatas34vcn9' THEN 'SEI'

end AS Pool,

'Close' AS type,

direction,

"close reason",

avg(pnl_usd) AS pnl_usd

from

osmosis.core.fact_msg_attributes m

inner join (

select

TX_ID,

MSG_TYPE,

ATTRIBUTE_VALUE AS direction

from

osmosis.core.fact_msg_attributes

where

MSG_TYPE like 'wasm-position-close%'

and ATTRIBUTE_KEY = 'direction'

) A on A.TX_ID = m.TX_ID

and A.MSG_TYPE = m.MSG_Type