Query to find the most expensive and slow queries in a BigQuery data warehouse over the last 7 days

bigquery-most-expensive.sql

SELECT
  job_id,
  user_email,
  TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) as job_time_ms,
  query,
  ROUND(total_bytes_processed / 1024 / 1024 / 1024, 3) as total_gb_processed,
  ROUND(total_bytes_billed / 1024 / 1024 / 1024, 3) as total_gb_billed,
  FORMAT("$%.2f", (total_bytes_billed / 1024 / 1024 / 1024 / 1024) * 6.25) as estimated_cost_usd,
  total_slot_ms,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_type = "QUERY"
  AND state = "DONE"
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY total_bytes_processed DESC
LIMIT 50;