WITH t0 AS (

SELECT signers[0]::string AS address

, DATE_TRUNC('month', block_timestamp)::date AS date

, MAX(CASE WHEN succeeded THEN 0 ELSE 1 END) AS has_fail

FROM solana.core.fact_transactions

WHERE block_timestamp >= '2021-01-01'

GROUP BY 1, 2

SELECT date

, 100 * AVG(has_fail) AS pct_with_fail

FROM t0

GROUP BY 1