Trade Offs Between Metrics and Guardrails Questions

WITH normalized_events AS (
  SELECT DISTINCT user_id,
         (occurred_at AT TIME ZONE 'UTC')::date AS day -- change 'UTC' to business timezone
  FROM events
),
daily_active AS (
  SELECT day, COUNT(DISTINCT user_id) AS active_users
  FROM normalized_events
  GROUP BY day
),
normalized_orders AS (
  SELECT DISTINCT user_id,
         (created_at AT TIME ZONE 'UTC')::date AS day -- same timezone
  FROM orders
),
daily_conversions AS (
  SELECT day, COUNT(DISTINCT user_id) AS converting_users
  FROM normalized_orders
  GROUP BY day
),
daily AS (
  SELECT d.day,
         COALESCE(active_users,0) AS active_users,
         COALESCE(converting_users,0) AS converting_users,
         CASE WHEN COALESCE(active_users,0)>0
              THEN converting_users::numeric / active_users
              ELSE NULL END AS conversion_rate
  FROM (
    SELECT day FROM daily_active
    UNION
    SELECT day FROM daily_conversions
  ) days
  LEFT JOIN daily_active d ON days.day = d.day
  LEFT JOIN daily_conversions c ON days.day = c.day
)
SELECT day,
       active_users,
       converting_users,
       ROUND(conversion_rate::numeric, 4) AS conversion_rate,
       ROUND(AVG(conversion_rate) OVER (ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING), 4) AS prior_7day_avg,
       CASE
         WHEN AVG(conversion_rate) OVER (ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) IS NULL THEN FALSE
         WHEN conversion_rate <= 0.8 * (AVG(conversion_rate) OVER (ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)) THEN TRUE
         ELSE FALSE
       END AS down_>=_20pct
FROM daily
ORDER BY day;