1 Hour Interval Average Load with Credits and Query count

one_hour_interval_average_load_with_credits_and_query_count.sql

WITH wlh as (
SELECT DATE_TRUNC('hour', wl.start_time) start_time_trunced_at_hour, HOUR(wl.start_time) start_time_hour,
AVG(avg_running) avg_running, AVG(avg_queued_load) avg_queued_load, 
AVG(avg_queued_provisioning) avg_queued_provisioning, AVG(avg_blocked) avg_blocked
FROM snowflake.account_usage.warehouse_load_history wl
WHERE DATE_TRUNC('DAY', wl.start_time) = '2020-03-26' 
AND wl.warehouse_name = 'PUT_YOUR_WAREHOUSE_NAME'
GROUP BY  start_time_trunced_at_hour, start_time_hour
ORDER BY start_time_trunced_at_hour asc
),
wmh as (
SELECT HOUR(wm.start_time) start_time_hour, credits_used
FROM snowflake.account_usage.warehouse_metering_history wm
WHERE DATE_TRUNC('DAY', wm.start_time) = '2020-03-26' 
AND wm.warehouse_name = 'PUT_YOUR_WAREHOUSE_NAME'
ORDER BY start_time_hour asc
),
qh as (
SELECT DATE_TRUNC('hour', qh.start_time) start_time_trunced_at_hour, HOUR(qh.start_time) start_time_hour,
COUNT(*) query_count,
AVG(compilation_time) avg_compilation_time,
AVG(execution_time) avg_execution_time,
AVG(queued_provisioning_time) avg_queued_provisioning_time,
AVG(queued_repair_time) avg_queued_repair_time,
AVG(queued_overload_time) avg_queued_overload_time
FROM snowflake.account_usage.query_history qh
WHERE DATE_TRUNC('DAY', qh.start_time) = '2020-03-26'
AND qh.warehouse_name = 'PUT_YOUR_WAREHOUSE_NAME'
GROUP BY  start_time_trunced_at_hour, start_time_hour
ORDER BY  start_time_trunced_at_hour
)
SELECT wlh.start_time_hour, 
wlh.avg_running, 
wlh.avg_queued_load, 
wlh.avg_queued_provisioning, 
wlh.avg_blocked,
wmh.credits_used, 
qh.query_count,
qh.avg_compilation_time,
qh.avg_execution_time,
qh.avg_queued_provisioning_time,
qh.avg_queued_repair_time,
qh.avg_queued_overload_time
FROM wlh, wmh, qh
WHERE wlh.start_time_hour = wmh.start_time_hour
AND qh.start_time_hour = wmh.start_time_hour