SELECT

DATE_TRUNC('month', BLOCK_TIMESTAMP_HOUR) AS month_block_time,

EXTRACT(MONTH FROM BLOCK_TIMESTAMP_HOUR) AS month_number,

EXTRACT(YEAR FROM BLOCK_TIMESTAMP_HOUR) AS year_number,

SUM(TRANSACTION_COUNT) AS total_transactions,

SUM(TRANSACTION_COUNT_SUCCESS) AS total_success

FROM

aptos.stats.ez_core_metrics_hourly

WHERE

YEAR(BLOCK_TIMESTAMP_HOUR) = YEAR(CURRENT_DATE())

AND MONTH(BLOCK_TIMESTAMP_HOUR) <= 9 -- Filter for months up to September

GROUP BY

month_block_time

SELECT

CASE

month_number

WHEN 1 THEN 'January'

WHEN 2 THEN 'February'

WHEN 3 THEN 'March'

WHEN 4 THEN 'April'

WHEN 5 THEN 'May'

WHEN 6 THEN 'June'

WHEN 7 THEN 'July'

WHEN 8 THEN 'August'

WHEN 9 THEN 'September'

END || ' ' || year_number AS month_name,

CASE

WHEN total_transactions > 0 THEN

(SUM(total_success) / SUM(total_transactions)) * 100

ELSE 0

END AS transaction_success_rate

FROM

monthly_data