dashboards/system_tables/sql/reservation_utilization_month.sql [32:70]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
WITH
  reservation_slot_capacity AS (
    SELECT
      -- Concatenation is needed as RESERVATION_CHANGES_BY_PROJECT only
      -- includes reservation name but in order to join with
      -- JOBS_BY_ORGANIZATION, reservation id is required
      CONCAT("{project_id}:{location}.", reservation_name) AS reservation_id,
      change_timestamp AS start_time,
      IFNULL(
        LEAD(change_timestamp)
          OVER (
            PARTITION BY reservation_name
            ORDER BY change_timestamp ASC),
        CURRENT_TIMESTAMP()) AS end_time,
      action,
      slot_capacity
    FROM
      `region-{region_name}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  ),
    -- This table retrieves only the current slot capacity of a reservation
  latest_slot_capacity AS (
    SELECT
      rcp.reservation_name,
      rcp.slot_capacity,
      CONCAT("{project_id}:{location}.", rcp.reservation_name) AS reservation_id,
    FROM
      `region-{region_name}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT AS rcp
    WHERE
      -- This subquery returns the latest slot capacity for each reservation
      -- by extracting the reservation with the maximum timestamp
      (rcp.reservation_name, rcp.change_timestamp) IN (
        SELECT AS STRUCT reservation_name, MAX(change_timestamp)
        FROM
          `region-{region_name}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
        GROUP BY reservation_name)
  )
-- Compute the average slot utilization and average reservation utilization
SELECT
  jbo.reservation_id,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



dashboards/system_tables/sql/reservation_utilization_week.sql [32:70]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
WITH
  reservation_slot_capacity AS (
    SELECT
      -- Concatenation is needed as RESERVATION_CHANGES_BY_PROJECT only
      -- includes reservation name but in order to join with
      -- JOBS_BY_ORGANIZATION, reservation id is required
      CONCAT("{project_id}:{location}.", reservation_name) AS reservation_id,
      change_timestamp AS start_time,
      IFNULL(
        LEAD(change_timestamp)
          OVER (
            PARTITION BY reservation_name
            ORDER BY change_timestamp ASC),
        CURRENT_TIMESTAMP()) AS end_time,
      action,
      slot_capacity
    FROM
      `region-{region_name}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  ),
  -- This table retrieves only the current slot capacity of a reservation
  latest_slot_capacity AS (
    SELECT
      rcp.reservation_name,
      rcp.slot_capacity,
      CONCAT("{project_id}:{location}.", rcp.reservation_name) AS reservation_id,
    FROM
      `region-{region_name}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT AS rcp
    WHERE
      -- This subquery returns the latest slot capacity for each reservation
      -- by extracting the reservation with the maximum timestamp
      (rcp.reservation_name, rcp.change_timestamp) IN (
        SELECT AS STRUCT reservation_name, MAX(change_timestamp)
        FROM
          `region-{region_name}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
        GROUP BY reservation_name)
  )
-- Compute the average slot utilization and average reservation utilization
SELECT
  jbo.reservation_id,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



