Uptime by hour using lateral joins

uptime_by_hour_lateral.sql

SELECT
    cal_date, 
    max(CASE WHEN hour =  0 THEN up_pct END) AS hour_0,
    max(CASE WHEN hour =  1 THEN up_pct END) AS hour_1,
    max(CASE WHEN hour =  2 THEN up_pct END) AS hour_2,
    max(CASE WHEN hour =  3 THEN up_pct END) AS hour_3,
    max(CASE WHEN hour =  4 THEN up_pct END) AS hour_4,
    max(CASE WHEN hour =  5 THEN up_pct END) AS hour_5,
    max(CASE WHEN hour =  6 THEN up_pct END) AS hour_6,
    max(CASE WHEN hour =  7 THEN up_pct END) AS hour_7,
    max(CASE WHEN hour =  8 THEN up_pct END) AS hour_8,
    max(CASE WHEN hour =  9 THEN up_pct END) AS hour_9,
    max(CASE WHEN hour = 10 THEN up_pct END) AS hour_10,
    max(CASE WHEN hour = 11 THEN up_pct END) AS hour_11,
    max(CASE WHEN hour = 12 THEN up_pct END) AS hour_12,
    max(CASE WHEN hour = 13 THEN up_pct END) AS hour_13,
    max(CASE WHEN hour = 14 THEN up_pct END) AS hour_14,
    max(CASE WHEN hour = 15 THEN up_pct END) AS hour_15,
    max(CASE WHEN hour = 16 THEN up_pct END) AS hour_16,
    max(CASE WHEN hour = 17 THEN up_pct END) AS hour_17,
    max(CASE WHEN hour = 18 THEN up_pct END) AS hour_18,
    max(CASE WHEN hour = 19 THEN up_pct END) AS hour_19,
    max(CASE WHEN hour = 20 THEN up_pct END) AS hour_20,
    max(CASE WHEN hour = 21 THEN up_pct END) AS hour_21,
    max(CASE WHEN hour = 22 THEN up_pct END) AS hour_22,
    max(CASE WHEN hour = 23 THEN up_pct END) AS hour_23
FROM (
    -- build virtual table of all hours between
    -- a date range
    SELECT
      start_ts,
      start_ts + interval '1 hour' AS end_ts
    FROM generate_series(
           '2017-03-01'::date,
           '2017-03-03'::timestamp - interval '1 hour',
           interval '1 hour'
         ) AS t(start_ts)
) AS cal
LEFT JOIN (
    -- build virtual table of uptimes
    SELECT *
    FROM (
      VALUES
      ('2017-03-01 01:15:00-06'::timestamp, '2017-03-01 02:15:00-06'::timestamp),
      ('2017-03-01 08:00:00-06', '2017-03-01 20:00:00-06'),
      ('2017-03-02 19:00:00-06', null)
    ) AS t(start_ts, end_ts)
) AS uptime ON cal.end_ts > uptime.start_ts AND cal.start_ts <= coalesce(uptime.end_ts, current_timestamp)
JOIN LATERAL (
    -- first level math, get actual start dates based on
    -- calendar time slice and uptime window.
    SELECT
      least(cal.end_ts, uptime.end_ts)                  AS last_ts,
      CASE WHEN uptime.start_ts IS NOT NULL THEN
        greatest(uptime.start_ts, cal.start_ts)
      END                                               AS first_ts,
      extract(epoch from age(cal.end_ts, cal.start_ts)) AS cal_seconds,
      extract(hour from cal.start_ts)                   AS hour,
      date_trunc('day', cal.start_ts)::date             AS cal_date
) t1 ON true
JOIN LATERAL (
    -- calculate uptime seconds
    SELECT
    coalesce
    (
      extract
      (
        epoch FROM age(last_ts, first_ts)
      ),
      0
    ) AS up_seconds
) t2 ON true
JOIN LATERAL (
    -- calculate percentage between uptime seconds and available seconds
    -- within the time slice
    SELECT up_seconds / cal_seconds AS up_pct
) t3 ON true
GROUP BY cal_date
;