Monitor the changes in BQ editions using AutoScaling. show for each change the allocation and the duration of the specific allocation with estimated cost

Monitor BQ edition autoscaling.sql

-- run from the project where the reservation is defined 
-- choose. the region DEFAULT: us
-- choose the time window dt_start and dt_end
-- set the project id

DECLARE
  dt_start DEFAULT TIMESTAMP('2023-05-01 00:00:00');
DECLARE
  dt_end DEFAULT TIMESTAMP('2023-05-01 23:59:00');
--SET @@dataset_project_id = 'my-project-id';

WITH
  slot_tdf AS (
  SELECT
    project_id,
    edition,
    reservation_name,
    TIMESTAMP_TRUNC(change_timestamp, SECOND) AS change_timestamp,
    TIMESTAMP_TRUNC(LEAD(change_timestamp) OVER (ORDER BY change_timestamp), SECOND) AS next_change,
    autoscale.current_slots AS allocated_slots,
    autoscale.max_slots AS max_slots,
    TIMESTAMP_DIFF(change_timestamp,LAG(change_timestamp) OVER (ORDER BY change_timestamp),SECOND)/60 AS minute_diff_lag,
    ABS(TIMESTAMP_DIFF(change_timestamp,LEAD(change_timestamp) OVER (ORDER BY change_timestamp),SECOND))/60 AS minute_diff_lead
  FROM
   `region-us`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  WHERE
    change_timestamp BETWEEN dt_start
    AND dt_end
  ORDER BY
    change_timestamp)
SELECT
  project_id,
  edition,
  reservation_name,
  change_timestamp,
  next_change,
  ROUND(minute_diff_lead,2) AS allocation_duration_minutes,
  allocated_slots AS allocated_slots,
  allocated_slots*CEIL(minute_diff_lead) AS estimated_total_slots_in_duration,
  max_slots
FROM
  slot_tdf limit 500