missed_hours AS (

SELECT hour,

DATEDIFF(hour, LAG(hour) OVER(ORDER BY hour ASC), hour) - 1 AS missed_swap_hours

FROM (

SELECT DISTINCT DATE_TRUNC(hour, block_timestamp) AS hour

FROM thorchain.core.fact_swaps_events

WHERE blockchain = 'ETH'

--Comment out the above line to remove the blockchain specificity.

)

WHERE hour BETWEEN '2022-05-10' AND '2023-05-11'

--A year's time for comparison with 365 * 24 hours per year.

QUALIFY missed_swap_hours > 0

SELECT SUM(missed_swap_hours) AS total_missed_swap_hours,

SUM(CASE WHEN missed_swap_hours >= 3 THEN missed_swap_hours ELSE 0 END) AS missed_swap_hours_over_threshold,

--Above, change the threshold for number of missed hours to count as non-'uptime'.

DATEDIFF(hour, MIN(hour), MAX(hour)) AS hour_range,

100 * (hour_range - total_missed_swap_hours) / hour_range AS uptime_percentage,

100 * (hour_range - missed_swap_hours_over_threshold) / hour_range AS threshold_uptime_percentage

FROM missed_hours