BigQuery Listing of Current Months' Queries Ordered by Cost, with Running Totals for Cost and % of All Cost

bigquery_monthy_query_costs.sql

with
  query_cost as (
  select
    date_trunc(date(creation_time),month) as billing_month,
    user_email,
    query,
    job_type,
    project_id,
    priority,
    concat(destination_table.dataset_id,'.',destination_table.table_id) as destination_table,
    sum(total_bytes_processed) as total_bytes_processed,
    avg(dml_statistics.inserted_row_count) as avg_inserted_row_count,
    avg(dml_statistics.deleted_row_count) as avg_deleted_row_count,
    avg(dml_statistics.updated_row_count) as avg_updated_row_count,
    count(distinct job_id) as query_runs,
    avg(unix_millis(end_time) - unix_millis(start_time))/1000 avg_runtime_secs,
    sum(total_bytes_billed) as total_bytes_billed,
    sum(total_bytes_billed)/1e12*5 sum_cost_usd,
    avg(total_bytes_billed)/1e12*5 avg_cost_usd
  from
    `region-europe-west2`.information_schema.jobs_by_project
  where
    date_trunc(date(creation_time),month) = date_trunc(current_date,month)
    and job_type = "QUERY"
  group by
    1,2,3,4,5,6,7
  order by
    15 desc),
  running_sum_cost as (
  select
    *,
    sum(sum_cost_usd) over (order by sum_cost_usd desc) as running_total_sum_cost_usd,
    sum_cost_usd/sum(sum_cost_usd) over () as pct_of_total
  from
    query_cost)
select
  *,
  sum(pct_of_total) over (order by sum_cost_usd desc) as running_total_pct_of_total
from
  running_sum_cost
order by
  sum_cost_usd desc