date,

NVL(label_type, 'unlabelled') as label_type,

COUNT(*) as occurrence

FROM (

SELECT

block_timestamp::date as date,

REPLACE(REPLACE(log_messages[0], 'Program '), ' invoke [1]') as program,

CASE

WHEN log_messages::string ILIKE '%sequence%' THEN REGEXP_SUBSTR(log_messages::string, 'sequence([^|]*)', 1, 1, 'i')

WHEN log_messages::string ILIKE '%error message:%' THEN REGEXP_SUBSTR(log_messages::string, 'error message:([^"]*)', 1, 1, 'i')

WHEN log_messages::string ILIKE '%errormessage:%' THEN REGEXP_SUBSTR(log_messages::string, 'errormessage:([^"]*)', 1, 1, 'i')

WHEN log_messages::string ILIKE '%error code:%' THEN REGEXP_SUBSTR(log_messages::string, 'error code:([^"]*)', 1, 1, 'i')

WHEN log_messages::string ILIKE '%errorcode:%' THEN REGEXP_SUBSTR(log_messages::string, 'errorcode:([^"]*)', 1, 1, 'i')

WHEN log_messages::string ILIKE '%custom program error:%' THEN 'custom program error' -- REGEXP_SUBSTR(log_messages::string, 'custom program error:([^"]*)', 1, 1, 'i') -- removed cause this causes too much stray data

WHEN log_messages::string ILIKE '%failed:%' THEN REGEXP_SUBSTR(log_messages::string, 'failed:([^"]*)', 1, 1, 'i')

ELSE 'Unknown'

END as error

FROM solana.core.fact_transactions

WHERE SUCCEEDED = FALSE

AND block_timestamp >= '2022-05-01'

AND block_timestamp < '2022-06-01'

) e

LEFT JOIN solana.core.dim_labels l

ON e.program = l.address

GROUP BY date, label_type

HAVING occurrence > {{occurs_more_than}} -- to be relevant

ORDER BY label_type