Workflow_BQ_get_BI_Engine_savings.sql

DECLARE var_day STRING DEFAULT '2022-01-09';
with t AS (
  SELECT
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime,
    5* (protopayload_auditlog.servicedata_v1_bigquery .jobCompletedEvent.job.jobStatistics. totalProcessedBytes/POWER(2,40) ) AS processedBytesCostProjection,
    5* (protopayload_auditlog.servicedata_v1_bigquery .jobCompletedEvent.job.jobStatistics. totalBilledBytes/POWER(2,40) ) AS billedBytesCostInUSD
  FROM
    `<dataset_auditlogs>.cloudaudit_googleapis_com_data_access_*`
  WHERE
    _TABLE_SUFFIX >= var_day and protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime>=TIMESTAMP(var_day)
    AND protopayload_auditlog.servicedata_v1_bigquery .jobCompletedEvent.eventName="query_job_completed"
    AND protopayload_auditlog.servicedata_v1_bigquery .jobCompletedEvent.job.jobStatistics.totalProcessedBytes IS NOT NULL
  )
SELECT
  TIMESTAMP_TRUNC(createTime, 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 1 DESC