BigDataDave1

	
--Create Table of Dates
CREATE OR REPLACE TABLE TBL_DATES (
    ID Integer,
    DATE_START Date,
    DATE_END Date,
    INSERT_DTS Timestamp_ltz(6)
);
 
--Insert Dates 1/1/2019 to 12/31/2020

BigDataDave1

--Create a UDF to Find Holiday Counts
CREATE OR REPLACE FUNCTION UDF_HOLIDAYS(DATESTART Date, DATEEND Date)
    RETURNS INTEGER
    AS
    $$
        SELECT COUNT(*) FROM TBL_HOLIDAYS
        WHERE DATE_HOLIDAY BETWEEN DATESTART AND DATEEND
    $$;
 
--Test UDF

BigDataDave1

--Put it Into a Stored Procedure
CREATE OR REPLACE PROCEDURE SP_UNLOAD_DYNAMIC()
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    //Unload JSON to Stage
    //COPY INTO OBJECT CONSTRUCT(*)
    //With Dynamic Pathing with JS MS TS for different unload naming convention

BigDataDave1

--SP for Feeding in Dynamic Path Variable
CREATE OR REPLACE PROCEDURE SP_UNLOAD_DYNAMIC_PATH(PATH STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    //Unload JSON to Stage
    //COPY INTO OBJECT CONSTRUCT(*)
    //With my path input variable unload naming convention

BigDataDave1

--Context Setting
use database sales;
use schema public;
use warehouse load_wh;

--Warehouse Params
show warehouses like 'LOAD_WH';
show parameters for warehouse load_wh;
alter warehouse load_wh set MAX_CONCURRENCY_LEVEL = 4;

BigDataDave1

SHOW WAREHOUSES;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_resume" = FALSE;

BigDataDave1

SHOW WAREHOUSES;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_suspend" IS NULL;

BigDataDave1

SHOW WAREHOUSES;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_suspend" > 600;  --Your Threshold in Seconds
 
--SQL Pro, just do all 3
SHOW WAREHOUSES;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_resume" = FALSE OR
"auto_suspend" IS NULL OR
"auto_suspend" > 600;

BigDataDave1

ALTER WAREHOUSE LOAD_WH SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;
SHOW PARAMETERS IN WAREHOUSE LOAD_WH;

BigDataDave1

SELECT WAREHOUSE_NAME, DATE(START_TIME) AS DATE, 
  SUM(CREDITS_USED) AS CREDITS_USED,
  AVG(SUM(CREDITS_USED)) OVER (PARTITION BY WAREHOUSE_NAME ORDER BY DATE ROWS 7 PRECEDING) AS CREDITS_USED_7_DAY_AVG,
  (TO_NUMERIC(SUM(CREDITS_USED)/CREDITS_USED_7_DAY_AVG*100,10,2)-100)::STRING || '%' AS VARIANCE_TO_7_DAY_AVERAGE
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"
GROUP BY DATE, WAREHOUSE_NAME
ORDER BY DATE DESC;