Query editor
withp_base as (SELECTowner,position_key,case when position_side = 1 then 'long' else 'short' end as position_side,custody_collateral_key,custody_position_key-- ,tx_id-- ,position_change,max(block_time) as block_time-- ,max(date_diff('day', date_trunc('day',block_time), date_trunc('day',now()))) as days_open --can't use this because positions can be reopened.,sum((case when position_change = 'increase' then 1 else -1 end)*size_usd) as size_usd,sum((case when position_change = 'increase' then 1 else -1 end)*collateral_usd - fee_usd) as collateral_usd --when you increase your position, the actual amount in is less fees. When you decrease your postiion, the transfer out is already less fees and pnl (pnl subtracted in query below) so we subtract again to get the correct pre-fee amount for accounting.,sum(fee_usd/(collateral_usd/collateral_token)) as fee_notional,sum(fee_usd) as fee_usd,sum(liq_fee_usd) as liq_fee_usd,(sum(size_usd*price_usd) filter (where position_change = 'increase'))/(sum(size_usd) filter (where position_change = 'increase')) as entry_price_usd --weighted average entry price of purchases only,sum((case when pnl_direction=0 then -1 else 1 end)*pnl_usd) as pnl_usd --taken in the form of the collateral tokenFROM query_3338071-- WHERE position_key = '54dZCAs4BJXhKMRCY6bkoFUrn8Lvz7wxmcoseYHvYsKx' --??? old idl I think https://solana.fm/tx/3WEjuaMWoHPcsscK6YWMsSwQuTqc2cthtXDNsjveL3NoAsVqqC7uY7AKe69ATW4LdJASfHCdyHVo8FP53jT44wTW?cluster=mainnet-qn1-- WHERE position_key = '6gzeXLNS96wVt5Gp8srGs33VD4zNxXXmPjjP11X13XFG' --complex QA, as of 1/13/2024-- WHERE position_key = 'AVewKrt37TvQ36DwGPqudPY9VqTfU261Sa31fgSpxpZC' --QAGROUP BY 1,2,3,4,5-- ,6,7,8)SELECTowner, position_key, position_side, sym.symbol as position_token, sym.token_mint_address as position_token_mint, tk_c.account_custodyTokenMint as position_collateral_mint, size_usd/(collateral_usd + COALESCE(pnl_usd,0)) as leverage, (case when position_side = 'short' then -1 else 1 end)*(COALESCE(p_p.price,dp_p.median_price) - entry_price_usd)*(size_usd/COALESCE(p_p.price,dp_p.median_price)) as gross_value_usd --unrealized pnl. (mark - entry)*notional size. short wants lower price to be profitable, long wants higher price., cast((case when position_side = 'short' then -1 else 1 end)*(COALESCE(p_p.price,dp_p.median_price) - entry_price_usd)*(size_usd/COALESCE(p_p.price,dp_p.median_price)) as double)/cast(size_usd as double) as gross_gain_pct --gain/size to get % gain, entry_price_usd, COALESCE(p_p.price,dp_p.median_price) as mark_price_usd, COALESCE(p_c.price,dp_c.median_price) as collateral_price_usd, size_usd, size_usd/COALESCE(p_p.price,dp_p.median_price) as size_notional, collateral_usd + COALESCE(pnl_usd,0) as collateral_usd --pnl is part of collateral change, (collateral_usd + COALESCE(pnl_usd,0))/COALESCE(p_c.price,dp_c.median_price) as collateral_notional, pnl_usd as realized_pnl_usd --realized pnl, fee_usd, liq_fee_usd-- --QA-- , sum(size_usd) over (order by block_time asc) as s_overall_usd-- , sum(collateral_usd + COALESCE(pnl_usd,0)) over (order by block_time asc) as c_overall_usd-- , position_change,block_time-- , tx_idFROM p_baseLEFT JOIN jupiter_solana.perpetuals_call_addCustody tk_p ON tk_p.account_custody = p_base.custody_position_keyLEFT JOIN jupiter_solana.perpetuals_call_addCustody tk_c ON tk_c.account_custody = p_base.custody_collateral_keyLEFT JOIN tokens_solana.fungible sym ON sym.token_mint_address = tk_p.account_custodyTokenMint--get prices