The following code creates a table that shows the comparison between On-Demand and Standard Edition, while the ratio between Slot cost to On-demand presents the estimated cost change moving from On-demand to Standard edition. Ratio value less than 100 shows the possibility of cost reduction, and above 100 shows the possibility to cost increase.

BigQuery OnDemand vs SE.sql

-- choose. the region DEFAULT: us
-- choose the time window dt_start and dt_end
-- In case it's not the current project set the dataset_project_id to the relevant project

DECLARE  current_on_demand_price_per_tb INT64 DEFAULT 5;
DECLARE  new_on_demand_price_per_tb FLOAT64 DEFAULT 6.25;
DECLARE standard_edition_price FLOAT64 DEFAULT 0.04;
DECLARE standard_edition_factor FLOAT64 DEFAULT 1.5;

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



WITH
  seconds AS (
  SELECT
    project_id,
    period_start,
    SUM(period_slot_ms) AS period_slot_ms,
    SUM(period_slot_ms/1000)/(60*60) AS slot_hour,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
  WHERE
    period_start BETWEEN dt_start AND dt_end
    AND reservation_id IS NULL
    AND job_id NOT LIKE 'script_%'
    AND period_slot_ms > 0
    AND total_bytes_billed > 0
    AND job_type ='QUERY'
  GROUP BY     project_id,period_start ),
  slots AS (
  SELECT
    project_id,
    TIMESTAMP_TRUNC(period_start, MONTH) AS period_start,
    ROUND(SUM(slot_hour), 2) AS total_slot_hours_usage,
    -- Calculating the diff between the avalaible committed slots to the usage (per minute)
  FROM     seconds
  GROUP BY     project_id,period_start   ORDER BY     period_start ASC),
 jobs AS (
  SELECT
    TIMESTAMP_TRUNC(creation_time, MONTH) AS creation_time,
    -- For on-demand projects the minimum bytes processed is 10MB, so need to account for that
    SUM(IF(COALESCE(total_bytes_billed, 0) / POW(1024, 2) < 10, 10*POW(1024, 2), COALESCE(total_bytes_billed, 0)) ) AS total_bytes_billed_on_demand,
  FROM
   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    creation_time BETWEEN dt_start AND dt_end
    AND reservation_id IS NULL
    AND job_id NOT LIKE 'script_%'
    AND total_slot_ms > 0     AND total_bytes_billed > 0  AND job_type = 'QUERY'
  GROUP BY
    project_id,TIMESTAMP_TRUNC(creation_time, MONTH))
  SELECT
  project_id,
  FORMAT_TIMESTAMP("%m-%Y", period_start) as month,
  ROUND(total_bytes_billed_on_demand / POW(1024, 4), 2) AS TiB_billed,
  ROUND(total_bytes_billed_on_demand / POW(1024, 4) * new_on_demand_price_per_tb, 2) AS new_on_demand_price,
  ROUND(total_slot_hours_usage, 2) AS total_slot_hours_usage,
  ROUND(total_slot_hours_usage*standard_edition_factor, 2) AS total_slot_hours_usage_factor,
  ROUND(total_slot_hours_usage*0.04, 2) AS total_slot_hours_cost,
  ROUND(total_slot_hours_usage*standard_edition_price*standard_edition_factor, 2) AS total_slot_hours_cost_factor_150,
  ROUND(ROUND(total_slot_hours_usage*standard_edition_price*standard_edition_factor, 2)/
        ROUND(total_bytes_billed_on_demand / POW(1024, 4) * new_on_demand_price_per_tb, 2),2)*100 AS ratio_percantage,
FROM
  jobs
JOIN
  slots
ON
  jobs.creation_time = slots.period_start
ORDER BY
  slots.period_start ASC;