SELECT

dayofweek(block_timestamp) as day_of_week

, HOUR(block_timestamp) as hour_of_day

, count(distinct tx_hash) as tx_count

FROM avalanche.core.fact_transactions

WHERE block_timestamp::date >= CURRENT_DATE-60

AND block_timestamp::date < CURRENT_DATE and STATUS ='FAIL'

GROUP BY 1, 2

CASE

WHEN day_of_week = 0 THEN '7_Sunday'

WHEN day_of_week = 1 THEN '6_Monday'

WHEN day_of_week = 2 THEN '5_Tuesday'

WHEN day_of_week = 3 THEN '4_Wednesday'

WHEN day_of_week = 4 THEN '3_Thursday'

WHEN day_of_week = 5 THEN '2_Friday'

WHEN day_of_week = 6 THEN '1_Saturday'

END as day_of_week

, hour_of_day

, tx_count as stpm

FROM n_

ORDER by 1, 2