Query editor
WITH pools AS (WITH base AS (SELECT'deposit' AS position_change,TO_BASE58(BYTEARRAY_SUBSTRING(data, 1 + 16, 32)) AS custody_key,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,block_slot,block_time,tx_idFROM solana.instruction_callsWHEREexecuting_account = 'PERPHjGBqRHArX4DySjwM6UJHiR3sWAatqfdBS2qQJu'AND BYTEARRAY_SUBSTRING(data, 1 + 8, 8) = 0x1bb299ba2fc48c2dAND tx_success = TRUEUNION 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,amount_out_pre_usd - amount_out_pre_usd / amount_out_pre * amount_out_post AS fee_usd,block_slot,block_time,tx_idFROM (SELECT'withdraw' AS position_change,TO_BASE58(BYTEARRAY_SUBSTRING(data, 1 + 16, 32)) AS custody_key,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,block_slot,block_time,tx_idFROM solana.instruction_callsWHEREexecuting_account = 'PERPHjGBqRHArX4DySjwM6UJHiR3sWAatqfdBS2qQJu'AND BYTEARRAY_SUBSTRING(data, 1 + 8, 8) = 0x8dc7b67b9f5ed766AND tx_success = TRUE))SELECTDATE_TRUNC('day', block_time) AS day,SUM(fee_usd) AS pool_feesFROM baseGROUP BY1), swaps AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(price * (amount_out - amount_out_post_fee) / POWER(10, decimals)) AS swap_feesFROM query_3379710