1 Hour Interval Average Load with Credits

one_hour_interval_average_load_with_credits.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)
SELECT wlh.*, wmh.credits_used
FROM   wlh, wmh
WHERE  wlh.start_time_hour = wmh.start_time_hour