program_name,

failed_message,

failed_txs,

(failed_txs * 1.0 / SUM(failed_txs) OVER ()) AS share

FROM (

SELECT

LEFT(flattened.value, POSITION(' failed' IN flattened.value) - 1) AS program_name,

SPLIT_PART(flattened.value, 'failed:', 2) AS failed_message,

COUNT(DISTINCT tx_id) AS failed_txs

FROM

solana.core.fact_transactions,

LATERAL FLATTEN(input => log_messages) AS flattened

WHERE

succeeded = 'FALSE'

AND block_timestamp > CAST('2024-03-09 23:59:59' AS TIMESTAMP)

AND block_timestamp <= CAST('2024-03-23 23:59:59' AS TIMESTAMP)

AND flattened.value LIKE '%failed:%'

AND LEFT(flattened.value, POSITION(' failed' IN flattened.value) - 1) = 'Program JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4' -- replace w/ your program, substitute for JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4 and leave "Program". Comment out line for no program filter

GROUP BY

LEFT(flattened.value, POSITION(' failed' IN flattened.value) - 1),

SPLIT_PART(flattened.value, 'failed:', 2)

) AS subquery

ORDER BY

failed_txs DESC;