Never miss a critical metric again.

-- SQL appears here

SELECT *
FROM (
  WITH
    `metric_calc_Collected` AS (
      SELECT SUM(`metric_value`) AS `CALCULATED_COLUMN_VALUE`
      FROM `myco`.`analytics`.`kpi_events`
      WHERE
        `event_time` >= TIMESTAMP_SUB(
          TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR),
          INTERVAL 3 HOUR
        )
        AND `event_time` <= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)
        AND `product_id` = 'checkout'
        AND `kpi_name` = 'transaction_volume'
    ),
    `main_metric` AS (
      SELECT `CALCULATED_COLUMN_VALUE` AS `METRIC_VALUE`
      FROM `metric_calc_Collected`
    ),
    `trend_time_windows` AS (
      SELECT
        TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL(i * 1 + 1) DAY) AS `WINDOW_END`,
        TIMESTAMP_SUB(
          TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL(i * 1 + 1) DAY),
          INTERVAL 3 HOUR
        ) AS `WINDOW_START`,
        EXTRACT(DAYOFWEEK FROM
          TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL(i * 1 + 1) DAY)
        ) AS `DOW`
      FROM UNNEST(GENERATE_ARRAY(0, 27)) AS i
    ),
    `trend_per_window` AS (
      SELECT w.`WINDOW_END`, SUM(`metric_value`) AS `CALCULATED_COLUMN_VALUE`
      FROM `myco`.`analytics`.`kpi_events` e
      JOIN `trend_time_windows` w
        ON w.`DOW` = EXTRACT(DAYOFWEEK FROM CURRENT_TIMESTAMP())
        AND w.`WINDOW_START` <= e.`event_time`
        AND w.`WINDOW_END` >= e.`event_time`
      WHERE `kpi_name` = 'transaction_volume'
      GROUP BY w.`WINDOW_END`
    ),
    `Compared_Trend` AS (
      SELECT
        AVG(`CALCULATED_COLUMN_VALUE`) AS `COMPARED_VALUE`,
        AVG(`CALCULATED_COLUMN_VALUE`) * (1 - 0.30) AS `COMPARED_VALUE_THRESHOLD`
      FROM `trend_per_window`
    )
  SELECT
    m.`METRIC_VALUE`,
    t.`COMPARED_VALUE`,
    t.`COMPARED_VALUE_THRESHOLD`
  FROM `main_metric` m
  LEFT JOIN `Compared_Trend` t ON TRUE
) AS subquery
LIMIT 1000