bigquery_information_schema_jobs_biengine_optimization.sql

DECLARE var_day STRING DEFAULT '2022-10-03';
with t as (
  SELECT creation_time,
      round(5* (total_bytes_processed/POWER(2,40) ),2) AS processedBytesCostProjection,
      round(5* (total_bytes_billed/POWER(2,40) ),2) AS billedBytesCostInUSD
  from `prj_id.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  where creation_time BETWEEN timestamp(var_day) and TIMESTAMP_add(timestamp(var_day), INTERVAL 1 DAY)
    and job_type = "QUERY" 
)
SELECT
  TIMESTAMP_TRUNC(creation_time, HOUR) AS hourly,
  ROUND(SUM(processedBytesCostProjection),2) AS processedBytesCostProjection,
  ROUND(SUM(billedBytesCostInUSD),2) AS billedBytesCostInUSD,
  ROUND(SUM(processedBytesCostProjection)-SUM(billedBytesCostInUSD),2) AS savings,
  cast(round(100-(SUM(billedBytesCostInUSD)*100/SUM(processedBytesCostProjection)),0) as STRING) || '%' as saving_percentage
FROM t
GROUP BY 1
ORDER BY saving_percentage DESC, hourly asc