The code creates a table below shows the actual Slot allocation and estimated cost in an hour or day resolution (not accurate since it depends on Autoscaler behaviour: https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#using_autoscaling_reservations)

Standard Edition cost estimation.sql

  -- choose. the region DEFAULT: us
  -- choose the time window start_time and end_time and
  -- choose if it's in a DAY or HOUR level aggregation of results
  -- set the project-id and run from the project where the reservation is defined


DECLARE SE_PRICE FLOAT64 DEFAULT 0.04;
DECLARE PAYG_PRICE FLOAT64 DEFAULT 0.06;
DECLARE start_time DEFAULT TIMESTAMP('2023-05-01 07:00:00');
DECLARE end_time DEFAULT TIMESTAMP('2023-06-30 07:00:00');
--DECLARE aggregation_window DEFAULT 'DAY';
DECLARE aggregation_window DEFAULT 'HOUR';
--SET @@dataset_project_id = 'my-project-id';

 
WITH autoscale_slot_data AS (
  SELECT
  project_id,
    change_timestamp,
    reservation_name,
    edition,
    CASE action
      WHEN "CREATE" THEN autoscale.current_slots
      WHEN "UPDATE" THEN
        IFNULL(autoscale.current_slots - LAG(autoscale.current_slots)
          OVER (PARTITION BY project_id, reservation_name ORDER BY change_timestamp ASC, action ASC),
        IFNULL(autoscale.current_slots,
        IFNULL(-1 * LAG(autoscale.current_slots)
          OVER (PARTITION BY project_id, reservation_name ORDER BY change_timestamp ASC, action ASC), 0)))
      WHEN "DELETE" THEN IF
    (LAG(action) OVER (PARTITION BY project_id, reservation_name ORDER BY change_timestamp ASC, action ASC)
        IN UNNEST(['CREATE', 'UPDATE']),
      -1 * autoscale.current_slots,
      0)
  END
    AS current_slot_delta,
    CASE action
      WHEN "CREATE" THEN slot_capacity
      WHEN "UPDATE" THEN
        IFNULL(slot_capacity - LAG(slot_capacity)
          OVER (PARTITION BY project_id, reservation_name ORDER BY change_timestamp ASC, action ASC),
        IFNULL(slot_capacity,
        IFNULL(-1 * LAG(slot_capacity)
          OVER (PARTITION BY project_id, reservation_name ORDER BY change_timestamp ASC, action ASC), 0)))
      WHEN "DELETE" THEN IF
    (LAG(action) OVER (PARTITION BY project_id, reservation_name ORDER BY change_timestamp ASC, action ASC) IN UNNEST(['CREATE', 'UPDATE']),
      -1 * slot_capacity,
      0)
  END
    AS baseline_slot_delta,
  FROM
    `region-us.INFORMATION_SCHEMA.RESERVATION_CHANGES`
  WHERE    
    change_timestamp <= end_time 
    ),
  running_total AS (
  SELECT
  project_id,
  edition,
  reservation_name,
    change_timestamp,
    SUM(current_slot_delta) OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_slots,
    SUM(baseline_slot_delta) OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS baseline_slots,
  FROM
    autoscale_slot_data),
/*SELECT
  *
FROM
  running_total
WHERE   change_timestamp between start_time and end_time
ORDER BY 
  change_timestamp
 */ 
 
SlotSeconds AS(
  SELECT
  project_id,
  edition,
  reservation_name,
    change_timestamp,
    (current_slots+baseline_slots) * TIMESTAMP_DIFF(LEAD(change_timestamp) OVER (ORDER BY change_timestamp), change_timestamp, MILLISECOND) AS slot_mill_seconds
  FROM
    running_total
  WHERE
    change_timestamp BETWEEN start_time
    AND end_time )
SELECT
project_id,
edition,
reservation_name,
    CASE aggregation_window
      WHEN 'HOUR' THEN TIMESTAMP_TRUNC(change_timestamp, HOUR) 
      WHEN 'DAY' THEN  TIMESTAMP_TRUNC(change_timestamp, DAY) 
     END AS agg_duration,
  SUM(slot_mill_seconds) as sum_slots_ms,
  ROUND(SUM(slot_mill_seconds)/1000/60/60) as sum_slot_hour,
  CASE edition
  WHEN 'STANDARD' THEN ROUND(SUM(slot_mill_seconds)/1000/60/60*SE_PRICE,2)
  WHEN 'ENTERPRISE' THEN ROUND(SUM(slot_mill_seconds)/1000/60/60*PAYG_PRICE,2)
  END as estimated_slot_cost
FROM
  SlotSeconds
  group by project_id,agg_duration, edition,reservation_name
  order by agg_duration