Query editor
-- Collector Crypt: daily overlay of treasury LP activity, bot tx health and buybacks-- One row per day. Read across a row to see whether an outage day had LP changes,-- failed bot txs, or simply no bot activity at all.WITH lp_ix AS (SELECTblock_date,tx_id,CASEWHEN bytearray_substring(data, 1, 8) IN (0x87802f4d0f98f031, 0x4db84ad67056f1c7, 0x4dffae527d1dc92e)THEN 'open_position'WHEN bytearray_substring(data, 1, 8) = 0x7b86510031446262THEN 'close_position'WHEN bytearray_substring(data, 1, 8) IN (0x2e9cf3760dcdfbb2, 0x851d59df45eeb00a)THEN 'add_liquidity'WHEN bytearray_substring(data, 9, 16) = 0x00000000000000000000000000000000THEN 'fee_claim'ELSE 'withdraw_liquidity'END AS event_typeFROM solana.instruction_callsWHERE executing_account = 'CAMMCzo5YL8w4VFF8KVHrK22GGUsp5VTaW7grrKgrWqK'AND block_date >= DATE '2026-04-19'AND tx_success = trueAND bytearray_substring(data, 1, 8) IN (0x87802f4d0f98f031, 0x4db84ad67056f1c7, 0x4dffae527d1dc92e,0x7b86510031446262,0x2e9cf3760dcdfbb2, 0x851d59df45eeb00a,0xa026d06f685b2c01, 0x3a7fbc3e4f52c460)AND contains(account_arguments, '3PnVBrb4wPLFLW38oaYR7dA6HSfKpawxHGESPj5kF1QB')AND ( contains(account_arguments, 'HnhpJPJgBG2KwniMTNW8cVBHvk1hFog3RC3kjnyc23tD')OR bytearray_substring(data, 1, 8) = 0x7b86510031446262 )),lp_daily AS (SELECTblock_date AS day,COUNT(DISTINCT CASE WHEN event_type = 'open_position' THEN tx_id END) AS lp_opens,COUNT(DISTINCT CASE WHEN event_type = 'close_position' THEN tx_id END) AS lp_closes,COUNT(DISTINCT CASE WHEN event_type = 'add_liquidity' THEN tx_id END) AS lp_adds,COUNT(DISTINCT CASE WHEN event_type = 'withdraw_liquidity' THEN tx_id END) AS lp_withdrawals,COUNT(DISTINCT CASE WHEN event_type = 'fee_claim' THEN tx_id END) AS lp_fee_claimsFROM lp_ixGROUP BY 1),bot_txs AS (SELECTblock_date AS day,COUNT_IF(success) AS bot_ok_txs,COUNT_IF(NOT success) AS bot_failed_txs,SUM(fee) / 1e9 AS bot_fees_solFROM solana.transactionsWHERE block_date >= DATE '2026-04-19'AND signer = '3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq'GROUP BY 1),funding_daily AS (SELECT