WITH msg1 AS(

SELECT count(tx_id) AS MSGCOUNT ,

WHEN POSITION('over its capacity' in TRANSACTION_RESULT)>0 THEN 'Capacity Error'

WHEN POSITION('does not have a valid signature' in TRANSACTION_RESULT)>0 THEN 'Signature Error'

WHEN POSITION('has sequence number' in TRANSACTION_RESULT)>0 THEN 'Sequence Error'

END AS ERRMSG

FROM flow.core.fact_transactions

WHERE TX_SUCCEEDED = 'false'

AND block_timestamp::date BETWEEN '2022-05-28' AND '2022-05-30'

AND LEFT(RIGHT(TRANSACTION_RESULT:error,LEN(TRANSACTION_RESULT:error)-POSITION('failed:' IN TRANSACTION_RESULT:error)-7),

POSITION( '-->' IN RIGHT(TRANSACTION_RESULT:error,LEN(TRANSACTION_RESULT:error)-POSITION('failed:' IN TRANSACTION_RESULT:error)-7) )-2) = ''

GROUP BY 2

),

MSG2 AS (

SELECT count(tx_id) AS MSGCOUNT ,

LEFT(RIGHT(TRANSACTION_RESULT:error,LEN(TRANSACTION_RESULT:error)-POSITION('failed:' IN TRANSACTION_RESULT:error)-7),

POSITION( '-->' IN RIGHT(TRANSACTION_RESULT:error,LEN(TRANSACTION_RESULT:error)-POSITION('failed:' IN TRANSACTION_RESULT:error)-7) )-2) AS errmsg

FROM flow.core.fact_transactions

WHERE TX_SUCCEEDED = 'false'

AND block_timestamp::date BETWEEN '2022-05-28' AND '2022-05-30'

AND errmsg != ''

GROUP BY 2

ORDER BY 1 DESC

SELECT *

FROM MSG1

UNION

SELECT *

FROM MSG2