Query editor
WITH ixs_a AS (-- Find all program invocations and track their depth/positionSELECTt.id,t.block_slot,SPLIT(logs1.log, ' ')[2] as program_id,TRY_CAST(substr(SPLIT(logs1.log, ' ')[4], 2, 1) as int) as depth,CARDINALITY(log_messages) as final_log_index,logs1.nr as "rn"FROM solana.transactions AS t,UNNEST (log_messages) WITH ORDINALITY logs1(log, nr)WHERE block_date = DATE '2024-01-16'/*AND SPLIT(logs1.log, ' ')[2] in ('JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4','whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc')*/and id = '3xSfKdCotov27zvLiCZ4CU2an8HkNQirBv2dMXZBH3V2kGiNuoqnJdY1ZU6CZpgNyJbx84gibyiFnuLcdNXEunN3'AND logs1.log LIKE 'Program % invoke [%]'),ixs AS (-- Find the next invocation at same or higher depth to establish scopeSELECTa.id,a.program_id,a.block_slot,a.depth,a.rn as "index",COALESCE(MIN(b.rn), a.final_log_index) as "nextIndex"FROM ixs_a aLEFT JOIN ixs_a b ONa.id = b.idAND a.rn < b.rnAND a.depth >= b.depthGROUP BY a.id, a.program_id, a.depth, a.rn, a.final_log_index, a.block_slot),consumedunits_a AS (-- Find all compute unit consumption logsSELECTt.id,SPLIT(logs2.log, ' ')[2] as program_id,TRY_CAST(SPLIT(logs2.log, ' ')[4] AS BIGINT) AS computed_units_spent,logs2.nr as "rn"FROM solana.transactions AS t,UNNEST (t.log_messages) WITH ORDINALITY logs2(log, nr)WHERE block_date = DATE '2024-01-16'/*AND SPLIT(logs2.log, ' ')[2] in ('JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4','whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc')*/and id = '3xSfKdCotov27zvLiCZ4CU2an8HkNQirBv2dMXZBH3V2kGiNuoqnJdY1ZU6CZpgNyJbx84gibyiFnuLcdNXEunN3'AND logs2.log LIKE 'Program % consumed % of % compute units'),consumedunits_b AS (-- Match each invocation with its compute units consumption-- The consumption log must appear between the invocation and the next invocation at same/higher depthSELECTix.id,ix.program_id,ix.index,ix.nextIndex,