date_trunc('hour', block_timestamp) AS hour,

tx_id,

tx_from,

fee / 1e6 AS fee,

CASE WHEN TX_Succeeded = 'TRUE' THEN 1 ELSE 0 END AS tx_status

FROM lava.core.fact_transactions

WHERE block_timestamp > '2024-07-30 11:00:00.000')

SELECT

hour,

COUNT(DISTINCT tx_id) AS txs,

COUNT(DISTINCT tx_from) AS users,

SUM(fee) AS fees,

SUM(tx_status) AS success_txs,

COUNT(*) - SUM(tx_status) AS failed_txs,

(SUM(tx_status) * 100 / COUNT(*)) AS success_rate,

((COUNT(*) - SUM(tx_status)) * 100 / COUNT(*)) AS failure_rate

FROM tx_data

GROUP BY 1

ORDER BY 1