SELECT

COUNT(DISTINCT TX_HASH) AS total_transactions,

COUNT(DISTINCT FROM_ADDRESS) AS total_users,

SUM(TX_FEE) AS total_fees,

AVG(TX_FEE) AS avg_tx_fee,

COUNT(DISTINCT CASE WHEN TX_SUCCEEDED = TRUE THEN TX_HASH END) AS successful_transactions,

COUNT(DISTINCT CASE WHEN TX_SUCCEEDED = FALSE THEN TX_HASH END) AS failed_transactions,

ROUND(

COUNT(DISTINCT CASE WHEN TX_SUCCEEDED = TRUE THEN TX_HASH END) * 100.0

/ NULLIF(COUNT(DISTINCT TX_HASH), 0), 2

) AS success_rate_percentage

FROM swell.core.fact_transactions

),

historical_totals AS (

SELECT

total_transactions,

total_users,

total_fees,

avg_tx_fee,

successful_transactions,

failed_transactions,

success_rate_percentage,

FROM total_transactions

SELECT

total_transactions,

total_users,

total_fees,

avg_tx_fee,

successful_transactions,

failed_transactions,

success_rate_percentage,

FROM historical_totals