Count(DISTINCT tx_hash) AS no_of_txs,

Sum(success) / ( Sum(success) + Sum(failed) ) * 100 AS success_percent,

Avg(success_percent)

OVER (

ORDER BY week) AS avg_sucess_rate,

Count(DISTINCT from_address) AS no_of_users,

Sum(tx_fee) AS txn_fees

FROM (SELECT *,

Iff(status = 'SUCCESS', '1', '0') AS success,

Iff(status = 'FALSE', '0', '1') AS failed

FROM optimism.core.fact_transactions)

WHERE week >= '2022-08-01' -- since Aug 2022

GROUP BY week

ORDER BY week DESC