SELECT block_timestamp::DATE AS date

, COUNT(DISTINCT tx_hash) AS tx

, COUNT(DISTINCT from_address) AS users

, SUM(tx_fee) AS eth_fees

, COUNT(CASE WHEN status = 'SUCCESS' THEN 1 ELSE NULL END) AS successful_tx

FROM optimism.core.fact_transactions

WHERE block_timestamp BETWEEN '2022-10-01' AND '2022-10-31'

GROUP BY 1

SELECT date

, tx-successful_tx AS "Failed Tx"

, successful_tx AS "Successful Tx"

, eth_fees/tx AS "Avg. ETH Fee/Tx"

, users AS "Users"

, successful_tx/tx*100 AS "Tx Success Rate"

, eth_fees AS "Daily ETH Fees"

FROM optimism_data