Metrics Selection and Diagnostic Interpretation Questions

from datetime import datetime, timezone
def normalize_ts(event_ts):
    now = datetime.now(timezone.utc)
    if abs((now - event_ts).total_seconds()) > 300:  # 5 min threshold
        return now, True  # use ingest time, mark anomaly
    return event_ts, False

SELECT service, COUNT(*) AS cnt
FROM events
WHERE ABS(EXTRACT(EPOCH FROM (ingest_time - event_time))) > 300
GROUP BY service;

-- hourly revenue and transaction counts for Region X for spike day vs previous week
SELECT
  date_trunc('hour', occurred_at AT TIME ZONE 'UTC' AT TIME ZONE 'RegionX_TZ') AS hour,
  SUM(amount) AS revenue,
  COUNT(DISTINCT order_id) AS orders,
  AVG(amount) AS avg_order
FROM orders
WHERE region = 'Region X'
  AND occurred_at >= '2025-12-01'::date - interval '7 days'
GROUP BY 1
ORDER BY 1;

-- verified signups
SELECT signup_date, COUNT(*) FROM users WHERE verified_at IS NOT NULL GROUP BY signup_date;

-- D7 retention
SELECT signup_date,
  COUNT(CASE WHEN activity_date BETWEEN signup_date+7 AND signup_date+7 THEN 1 END)::float / COUNT(*) AS d7_retention
FROM users JOIN user_activity ON users.id = user_activity.user_id GROUP BY signup_date;

-- duplicate rate
SELECT signup_date, SUM(is_duplicate::int)/COUNT(*) FROM users GROUP BY signup_date;

WITH params AS (
  SELECT 
    DATE '2025-01-01' AS start_a, DATE '2025-01-31' AS end_a,
    DATE '2025-02-01' AS start_b, DATE '2025-02-28' AS end_b
),
-- sessions in window A/B with join to users for country
sess AS (
  SELECT s.session_id, s.user_id, u.country, s.platform, s.traffic_source,
         CASE 
           WHEN s.started_at BETWEEN p.start_a AND p.end_a THEN 'A'
           WHEN s.started_at BETWEEN p.start_b AND p.end_b THEN 'B'
         END AS window
  FROM sessions s
  CROSS JOIN params p
  JOIN users u ON u.user_id = s.user_id
  WHERE s.started_at BETWEEN p.start_a AND p.end_a
     OR s.started_at BETWEEN p.start_b AND p.end_b
),
-- mark whether session had a purchase
conv AS (
  SELECT ss.session_id, ss.country, ss.platform, ss.traffic_source, ss.window,
         CASE WHEN MAX(CASE WHEN e.event_type = 'purchase' AND e.occurred_at BETWEEN p.start_a AND p.end_a THEN 1
                            WHEN e.event_type = 'purchase' AND e.occurred_at BETWEEN p.start_b AND p.end_b THEN 1
                       END) = 1 THEN 1 ELSE 0 END AS had_purchase
  FROM sess ss
  LEFT JOIN events e ON e.session_id = ss.session_id
  CROSS JOIN params p
  GROUP BY ss.session_id, ss.country, ss.platform, ss.traffic_source, ss.window
),
-- aggregate per segment & window
agg AS (
  SELECT country, platform AS os, traffic_source,
         window,
         COUNT(DISTINCT session_id) AS sessions,
         SUM(had_purchase) AS conversions,
         CASE WHEN COUNT(DISTINCT session_id) = 0 THEN 0.0
              ELSE SUM(had_purchase)::numeric / COUNT(DISTINCT session_id)
         END AS conv_rate
  FROM conv
  GROUP BY country, platform, traffic_source, window
)
SELECT
  a.country,
  a.os,
  a.traffic_source,
  COALESCE(a.sessions,0) AS sessions_a,
  COALESCE(a.conversions,0) AS conv_a,
  ROUND(COALESCE(a.conv_rate,0)::numeric, 4) AS conv_rate_a,
  COALESCE(b.sessions,0) AS sessions_b,
  COALESCE(b.conversions,0) AS conv_b,
  ROUND(COALESCE(b.conv_rate,0)::numeric, 4) AS conv_rate_b,
  (COALESCE(b.conv_rate,0) - COALESCE(a.conv_rate,0)) AS absolute_change,
  CASE WHEN COALESCE(a.conv_rate,0) = 0 THEN NULL
       ELSE ROUND( (COALESCE(b.conv_rate,0) - COALESCE(a.conv_rate,0)) / a.conv_rate, 4 )
  END AS relative_change
FROM
  (SELECT * FROM agg WHERE window = 'A') a
FULL OUTER JOIN
  (SELECT * FROM agg WHERE window = 'B') b
USING (country, os, traffic_source)
ORDER BY country, os, traffic_source;