Adding ability to add dynamic threshold for a user defined metric/check

[sutaraj01]

My requirements are to measure counts for a given time period (e.g. 10am-11am) over last 7 weeks (can be customised as well) for the same time period and I think the only way meaningfully achieve it would be an user defined check, however struggling to put a threshold around it. Many a times the counts or other metrics are cyclic patterns. It would be nice if we can put dynamic thresholds for the user defined checks.

For e.g, if i can just store the average value from this scan which averages for the same time period over last 7 weeks and use the dynamic thresholds to identify if there are any problem. My warehouse is Snowflake

checks for reservation_v:
  - row_count_avg_last7week_sametime < 100:
      name: DIFFERENCE OF ROW COUNT NOW AND ROW COUNT AVG OVER THE SAME TIME PERIOD FOR LAST 7 WEEKS < 100
      row_count_avg_last7week_sametime query: |
        WITH DATES_LAST7WEEKS
        (CURRENT_TIMESTAMP_LOW, CURRENT_TIMESTAMP_HIGH, WEEK1_LOW, WEEK1_HIGH, WEEK2_LOW, WEEK2_HIGH, WEEK3_LOW, WEEK3_HIGH, WEEK4_LOW, WEEK4_HIGH, WEEK5_LOW, WEEK5_HIGH, WEEK6_LOW, WEEK6_HIGH, WEEK7_LOW, WEEK7_HIGH)
        AS(
        SELECT
        DATEADD('HOUR', -2, CURRENT_TIMESTAMP()) AS "CURRENT_TIMESTAMP_LOW",
        DATEADD('HOUR', -1, CURRENT_TIMESTAMP()) AS "CURRENT_TIMESTAMP_HIGH",
        date_trunc('HOUR',dateadd(week,-1, "CURRENT_TIMESTAMP_LOW")) AS WEEK1_LOW,
        date_trunc('HOUR',dateadd(week,-1, "CURRENT_TIMESTAMP_HIGH")) AS WEEK1_HIGH,
        date_trunc('HOUR',dateadd(week,-2, "CURRENT_TIMESTAMP_LOW")) AS WEEK2_LOW,
        date_trunc('HOUR',dateadd(week,-2, "CURRENT_TIMESTAMP_HIGH")) AS WEEK2_HIGH,
        date_trunc('HOUR',dateadd(week,-3, "CURRENT_TIMESTAMP_LOW")) AS WEEK3_LOW,
        date_trunc('HOUR',dateadd(week,-3, "CURRENT_TIMESTAMP_HIGH")) AS WEEK3_HIGH,
        date_trunc('HOUR',dateadd(week,-4, "CURRENT_TIMESTAMP_LOW")) AS WEEK4_LOW,
        date_trunc('HOUR',dateadd(week,-4, "CURRENT_TIMESTAMP_HIGH")) AS WEEK4_HIGH,
        date_trunc('HOUR',dateadd(week,-5, "CURRENT_TIMESTAMP_LOW")) AS WEEK5_LOW,
        date_trunc('HOUR',dateadd(week,-5, "CURRENT_TIMESTAMP_HIGH")) AS WEEK5_HIGH,
        date_trunc('HOUR',dateadd(week,-6, "CURRENT_TIMESTAMP_LOW")) AS WEEK6_LOW,
        date_trunc('HOUR',dateadd(week,-6, "CURRENT_TIMESTAMP_HIGH")) AS WEEK6_HIGH,
        date_trunc('HOUR',dateadd(week,-7, "CURRENT_TIMESTAMP_LOW")) AS WEEK7_LOW,
        date_trunc('HOUR',dateadd(week,-7, "CURRENT_TIMESTAMP_HIGH")) AS WEEK7_HIGH
        )
        SELECT (SELECT COUNT(ref) FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT CURRENT_TIMESTAMP_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT CURRENT_TIMESTAMP_HIGH FROM DATES_LAST7WEEKS)) - ROUND(AVG(COUNT)) FROM (
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK1_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK1_HIGH FROM DATES_LAST7WEEKS)
        UNION
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK2_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK2_HIGH FROM DATES_LAST7WEEKS)
        UNION
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK3_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK3_HIGH FROM DATES_LAST7WEEKS)
        UNION
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK4_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK4_HIGH FROM DATES_LAST7WEEKS)
        UNION
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK5_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK5_HIGH FROM DATES_LAST7WEEKS)
        UNION
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK6_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK6_HIGH FROM DATES_LAST7WEEKS)
        UNION
        SELECT COUNT(ref) AS "COUNT" FROM PUBLIC.RESERVATION_V WHERE RES_DATE >= (SELECT WEEK7_LOW FROM DATES_LAST7WEEKS) AND RES_DATE < (SELECT WEEK7_HIGH FROM DATES_LAST7WEEKS)
        )