SELECT

date_trunc({{period_type}}, BLOCK_TIMESTAMP) AS "Date",

COUNT(DISTINCT tx_hash) AS "Total Transactions",

COUNT(DISTINCT CASE WHEN TX_SUCCEEDED = 'TRUE' THEN tx_hash END) AS "Total Succeeded Transactions"

FROM

berachain.testnet.fact_transactions

WHERE

BLOCK_TIMESTAMP::date >= '{{start_day}}'

AND date_trunc({{period_type}}, BLOCK_TIMESTAMP) <= '{{target_day}}'

GROUP BY 1

SELECT

"Date",

ROUND(100 * "Total Succeeded Transactions" / NULLIF("Total Transactions", 0), 2) AS "Transaction Success Rate %",

LAG(ROUND(100 * "Total Succeeded Transactions" / NULLIF("Total Transactions", 0), 2)) OVER (ORDER BY "Date") AS lag_success_rate,

ROUND(100 * (ROUND(100 * "Total Succeeded Transactions" / NULLIF("Total Transactions", 0), 2) - lag_success_rate) / NULLIF(lag_success_rate, 0), 2) AS "Transaction Success Rate Change %"

FROM transaction_data

ORDER BY 1 DESC