SELECT

DATE_TRUNC('day', t.block_timestamp) as tx_date,

t.signers[0] as signer,

COUNT(*) as attempt_count,

SUM(CASE WHEN t.succeeded = 'FALSE' THEN 1 ELSE 0 END) as failed_count

FROM solana.core.fact_transactions t,

LATERAL FLATTEN(input => log_messages) AS flattened

WHERE t.block_timestamp > CAST('2025-01-16 23:59:59' AS TIMESTAMP)

AND t.block_timestamp <= CAST('2025-01-20 23:59:59' AS TIMESTAMP)

AND flattened.value LIKE '%failed:%'

AND LEFT(flattened.value, POSITION(' failed' IN flattened.value) - 1) = 'Program JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4'

GROUP BY 1, 2

),

bucketed_attempts AS (

SELECT

tx_date,

CASE

WHEN attempt_count BETWEEN 1 AND 5 THEN '1-5'

WHEN attempt_count BETWEEN 6 AND 10 THEN '6-10'

WHEN attempt_count BETWEEN 11 AND 50 THEN '11-50'

WHEN attempt_count BETWEEN 51 AND 100 THEN '51-100'

WHEN attempt_count BETWEEN 101 AND 1000 THEN '101-1000'

ELSE '1000+'

END as attempt_bucket,

COUNT(*) as unique_signers,

SUM(failed_count) as total_failed_txs

FROM daily_signer_attempts

GROUP BY 1, 2

SELECT

tx_date,

attempt_bucket,

unique_signers,

total_failed_txs,

total_failed_txs * 1.0 / SUM(total_failed_txs) OVER (PARTITION BY tx_date) as failure_share