Query editor
withpools as (WITHbase as (SELECT'deposit' AS position_change, to_base58(bytearray_substring(data,1+16,32)) as custody_key --ties to the token mint of token, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+80,8))) as amount_in_pre, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+120,8))) as amount_in_post, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+104,8)))/1e6 as amount_in_pre_usd, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+128,8)))/1e6 as amount_in_post_usd, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+104,8)))/1e6- bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+128,8)))/1e6 as fee_usd --pre minus post-- , data, block_slot, block_time, tx_idFROM solana.instruction_callsWHERE executing_account = 'PERPHjGBqRHArX4DySjwM6UJHiR3sWAatqfdBS2qQJu'AND bytearray_substring(data,1+8,8) = 0x1bb299ba2fc48c2d -- AddLiquidityEventAND tx_success = true-- AND block_time >= DATE_TRUNC('day', NOW() - INTERVAL '90' day)-- AND tx_id = '2XMubBYscba1WwofDcXwvLzqqNRopeM9YCYMXVjRKNk3fpyoNwmBNowSFuyyyo6EHfc7H9W7bpMwUwzLnSMK4dcs'-- and block_slot = 234692429UNION ALLSELECTposition_change, custody_key, amount_out_pre, amount_out_post, amount_out_pre_usd, amount_out_pre_usd/amount_out_pre*amount_out_post as amount_out_post_usd --get price of token amount and then multiply it by notional post out, amount_out_pre_usd - amount_out_pre_usd/amount_out_pre*amount_out_post as fee_usd --pre minus post, block_slot, block_time, tx_idFROM (SELECT'withdraw' AS position_change, to_base58(bytearray_substring(data,1+16,32)) as custody_key --ties to the token mint of token, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+104,8))) as amount_out_pre, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+112,8))) as amount_out_post, bytearray_to_bigint(bytearray_reverse(bytearray_substring(data,1+88,8)))/1e6 as amount_out_pre_usd-- , data, block_slot, block_time, tx_idFROM solana.instruction_callsWHERE executing_account = 'PERPHjGBqRHArX4DySjwM6UJHiR3sWAatqfdBS2qQJu'-- AND bytearray_substring(data,1,8) = 0xe445a52e51cb9a1dAND bytearray_substring(data,1+8,8) = 0x8dc7b67b9f5ed766 -- RemoveLiquidityEventAND tx_success = true-- AND block_time >= DATE_TRUNC('day', NOW() - INTERVAL '90' day)-- AND tx_id = '22mXxgZ9ce5LFq4XYPAiNvi8x6So1bQsqHfBvWAhJdRXjH83Vvn5We8A1kMYofmb6w4qL6xNosDV6Ue7nUcvtac7'-- and block_slot = 241187012))