Metric Monitoring and Segmentation Questions

WITH params AS (
  -- define "today" and compute last two complete ISO weeks
  SELECT current_date AS today,
         date_trunc('week', current_date)::date - interval '7 days' AS last_week_start,
         date_trunc('week', current_date)::date - interval '14 days' AS prev_week_start
),
events_clean AS (
  SELECT
    user_id,
    event_type,
    COALESCE(amount, 0)::numeric AS amount,            -- treat null amounts as 0
    channel,
    occurred_at::date AS occurred_at
  FROM events
  WHERE event_type = 'purchase'                        -- assume only purchases count as revenue
),
weekly_rev AS (
  SELECT
    e.channel,
    CASE
      WHEN e.occurred_at >= p.last_week_start AND e.occurred_at < p.last_week_start + interval '7 days' THEN 'last_week'
      WHEN e.occurred_at >= p.prev_week_start AND e.occurred_at < p.prev_week_start + interval '7 days' THEN 'prev_week'
      ELSE NULL
    END AS week_label,
    SUM(e.amount) AS revenue
  FROM events_clean e
  CROSS JOIN params p
  WHERE e.occurred_at >= p.prev_week_start
    AND e.occurred_at < p.last_week_start + interval '7 days'
  GROUP BY 1,2
),
pivot AS (
  SELECT
    channel,
    COALESCE(MAX(CASE WHEN week_label='prev_week' THEN revenue END),0) AS prev_revenue,
    COALESCE(MAX(CASE WHEN week_label='last_week' THEN revenue END),0) AS last_revenue
  FROM weekly_rev
  GROUP BY channel
),
channel_changes AS (
  SELECT
    channel,
    prev_revenue,
    last_revenue,
    last_revenue - prev_revenue AS delta,
    CASE WHEN prev_revenue = 0 THEN NULL ELSE (last_revenue - prev_revenue)/prev_revenue END AS pct_change
  FROM pivot
),
overall AS (
  SELECT SUM(prev_revenue) AS total_prev, SUM(last_revenue) AS total_last FROM pivot
)
SELECT
  cc.channel,
  cc.prev_revenue,
  cc.last_revenue,
  cc.delta,
  cc.pct_change
FROM channel_changes cc
CROSS JOIN overall o
WHERE o.total_last < o.total_prev                -- only when overall revenue dropped
ORDER BY cc.delta ASC                             -- most negative contributions first
LIMIT 5;

-- events
SELECT country, event_date, COUNT(*) AS events, SUM(amount) AS events_amount
FROM events_dw
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY country, event_date;

-- billing
SELECT country, invoice_date AS event_date, COUNT(*) AS invoices, SUM(amount) AS billed_amount
FROM billing
WHERE invoice_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY country, invoice_date;

SELECT e.country, e.event_date,
       e.events, b.invoices,
       e.events_amount, b.billed_amount,
       (b.billed_amount - e.events_amount) AS amt_delta
FROM events_agg e
FULL OUTER JOIN billing_agg b USING (country, event_date)
ORDER BY ABS(amt_delta) DESC;

SELECT SUM(amt_delta) AS total_bias, SUM(billed_amount) AS total_billed,
       SUM(amt_delta)/NULLIF(SUM(billed_amount),0) AS pct_missing
FROM joined_agg
WHERE country = 'X';

-- validation example
SELECT COUNT(*) FROM events_dw_backfill WHERE country='X' AND event_date BETWEEN ...