WITH success_transactions AS (
SELECT to_date(block_timestamp) as date, count(tx_hash) as success_txns
FROM optimism.core.fact_transactions
WHERE status = 'SUCCESS'
GROUP BY date
), no_of_txns as (
SELECT to_date(block_timestamp) as date, count(tx_hash) as total_no_of_txns
FROM optimism.core.fact_transactions
GROUP BY date
)
SELECT
no_of_txns.date,(success_txns/total_no_of_txns) * 100 as transactions_success_rate
FROM no_of_txns
left join success_transactions
on no_of_txns.date = success_transactions.date
where no_of_txns.date >= current_date - 30