SELECT

HOUR(DATE_TRUNC('HOUR', BLOCK_TIMESTAMP::time)) AS HOUR,

SUM(FEE) AS total_fee,

COUNT(TX_ID) AS count

FROM flipside_prod_db.algorand.transactions

WHERE BLOCK_TIMESTAMP::date = '2022-01-07'

GROUP BY 1

ORDER BY 1 ASC

), max AS (

SELECT max(total_fee) AS max_fee FROM data

CAST(HOUR AS INT),

CASE

WHEN HOUR + 1=24 THEN '23:00 - 00:00'

ELSE

CONCAT(HOUR, ':00', ' - ', HOUR(TIME(CONCAT(HOUR + 1, ':', '00:00'))), ':00')

END AS HOUR_RANGE,

total_fee,

count,

SUM(total_fee) OVER(ORDER BY CAST(HOUR AS INT) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

CASE

WHEN total_fee > (SELECT max_fee FROM max)*0.75 THEN 'Peak'

ELSE 'Normal'

END AS peak

FROM data

ORDER BY CAST(HOUR AS INT) ASC