slot_utilization.sql

SELECT
COUNT(*) TOTAL_QUERIES, 
SUM(total_slot_ms/TIMESTAMP_DIFF(end_time,creation_time,MILLISECOND)) AVG_SLOT_USAGE,
SUM(TIMESTAMP_DIFF(end_time,creation_time,SECOND)) TOTAL_DURATION_IN_SECONDS,
AVG(TIMESTAMP_DIFF(end_time,creation_time,SECOND)) AVG_DURATION_IN_SECONDS,
SUM(total_bytes_processed*10e-12) TOTAL_PROCESSED_TB,
EXTRACT (DATE FROM creation_time) AS EXECUTION_DATE,
user_email as USER
FROM `iwinner-data-318822.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE state='DONE'
AND statement_type='SELECT'
AND creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP()
GROUP BY EXECUTION_DATE, USER
ORDER BY EXECUTION_DATE,AVG_SLOT_USAGE