Uptime by hour using sub queries

uptime_by_hour_subqueries.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 (
    SELECT
      -- calculate percentage between uptime seconds and available seconds
      -- within the time slice
      up_seconds / cal_seconds AS up_pct,
      *
    FROM (
        SELECT
            -- calculate uptime seconds
            coalesce
            (
              extract
              (
                epoch FROM age(last_ts, first_ts)
              ),
              0
            ) AS up_seconds,
            *
        FROM (
            SELECT
                -- first level math, get actual start dates based on
                -- calendar time slice and uptime window.
                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,
                *
            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)
        ) t1
    ) t2
) t3
GROUP BY cal_date
;