Metric Definition and Implementation Questions

-- Assertion: fail when today's orders < 50% of 7-day median
WITH daily_counts AS (
  SELECT
    DATE_TRUNC('day', occurred_at AT TIME ZONE 'UTC')::date AS day,
    COUNT(DISTINCT order_id) AS orders
  FROM orders
  WHERE occurred_at >= (CURRENT_DATE - INTERVAL '14 days') -- keep window small for performance
  GROUP BY 1
),
median_7 AS (
  SELECT
    percentile_cont(0.5) WITHIN GROUP (ORDER BY orders) AS median_orders
  FROM daily_counts
  WHERE day >= CURRENT_DATE - INTERVAL '8 days'  -- days -8..-1 (last 7 full days)
    AND day < CURRENT_DATE
),
today_count AS (
  SELECT COUNT(DISTINCT order_id) AS orders_today
  FROM orders
  WHERE DATE_TRUNC('day', occurred_at AT TIME ZONE 'UTC')::date = CURRENT_DATE
)
SELECT
  t.orders_today,
  m.median_orders,
  CASE
    WHEN m.median_orders IS NULL OR m.median_orders < 10 THEN 'SKIP_LOW_VOLUME' -- guardrail
    WHEN t.orders_today < 0.5 * m.median_orders THEN 'ALERT'
    ELSE 'PASS'
  END AS assertion_result
FROM today_count t CROSS JOIN median_7 m;

-- daily unique users (materialized table/view)
CREATE TABLE daily_users AS
SELECT
  DATE(event_time) AS day,
  user_id
FROM events
GROUP BY DATE(event_time), user_id;
-- add index on (day, user_id)

SELECT
  d.day,
  COUNT(DISTINCT du.user_id) AS mau_30
FROM (
  SELECT DISTINCT day FROM daily_users
) d
JOIN daily_users du
  ON du.day BETWEEN d.day - INTERVAL '29 days' AND d.day
GROUP BY d.day
ORDER BY d.day;