Metric Definition and Implementation Questions

-- DAU for last 30 days (including days with zero)
WITH days AS (
  SELECT (current_date - s)::date AS day
  FROM generate_series(0, 29) AS s
),
clean_events AS (
  SELECT
    (COALESCE(user_id::text, anon_id)) AS person_id, -- cast user_id to text if it's numeric
    (event_timestamp AT TIME ZONE 'UTC')::date AS event_date
  FROM events
  WHERE event_timestamp >= (current_date - INTERVAL '29 days')::timestamp
    AND event_timestamp < (current_date + INTERVAL '1 day')::timestamp
    AND properties->>'is_test' IS DISTINCT FROM 'true'  -- excludes explicit 'true'
    AND (user_id IS NOT NULL OR anon_id IS NOT NULL)
)
SELECT
  d.day,
  COALESCE(e.dau, 0) AS dau
FROM days d
LEFT JOIN (
  SELECT event_date, COUNT(DISTINCT person_id) AS dau
  FROM clean_events
  GROUP BY event_date
) e ON e.event_date = d.day
ORDER BY d.day DESC;

-- dedupe staging from raw streams
WITH combined AS (
  SELECT 'web' AS channel, event_id, user_id, device_id, timestamp AT TIME ZONE 'UTC' AS event_ts,
         properties->>'order_id' AS order_id,
         (properties->>'amount')::numeric AS amount,
         properties AS props, received_at
  FROM raw.web_events
  UNION ALL
  SELECT 'mobile', event_id, user_id, device_id, timestamp AT TIME ZONE 'UTC', 
         properties->>'order_id', (properties->>'amount')::numeric, properties, received_at
  FROM raw.mobile_events
),
priority AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY COALESCE(order_id, user_id||'|'||device_id||'|'||date_trunc('minute', event_ts)::text||'|'||amount)
      ORDER BY CASE WHEN props->>'server_verified'='true' THEN 0 ELSE 1 END, received_at DESC
    ) AS rn
  FROM combined
)
INSERT INTO analytics.canonical_purchases (...)
SELECT ... FROM priority WHERE rn = 1
ON CONFLICT (order_id) DO UPDATE
  SET amount = EXCLUDED.amount, channel = EXCLUDED.channel,
      props = EXCLUDED.props, source_received_ts = EXCLUDED.source_received_ts
  WHERE analytics.canonical_purchases.source_received_ts < EXCLUDED.source_received_ts;

SELECT
  user_id,
  DATE(event_ts) AS event_date,
  COUNT(*) AS clicks,
  COUNT(DISTINCT session_id) AS sessions,
  SUM(CASE WHEN is_purchase THEN amount ELSE 0 END) AS revenue
FROM events
WHERE event_type = 'click'
  AND event_ts >= '2025-01-01'
GROUP BY user_id, DATE(event_ts);

WITH months AS (
  -- generate first day of each month for last 13 months (so we can compute change for 12 months)
  SELECT generate_series(date_trunc('month', current_date) - interval '12 months',
                         date_trunc('month', current_date),
                         interval '1 month')::date AS month_start
),
monthly_rev AS (
  SELECT
    m.month_start,
    COALESCE(SUM(o.order_amount), 0) AS revenue
  FROM months m
  LEFT JOIN orders o
    ON o.created_at >= m.month_start
   AND o.created_at < (m.month_start + interval '1 month')
  GROUP BY m.month_start
),
mom AS (
  SELECT
    month_start,
    revenue,
    LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
    -- flag if current month is partial: month_start = first day of current month AND today < last day
    CASE
      WHEN month_start = date_trunc('month', current_date)
           AND current_date < (date_trunc('month', current_date) + interval '1 month' - interval '1 day')::date
      THEN true
      ELSE false
    END AS is_partial_month
  FROM monthly_rev
)
SELECT
  month_start,
  to_char(month_start, 'YYYY-MM') AS month_label,
  revenue,
  prev_revenue,
  -- percent change: NULL when prev_revenue is 0 to avoid division by zero
  CASE
    WHEN prev_revenue = 0 THEN NULL
    ELSE round(100.0 * (revenue - prev_revenue) / prev_revenue, 2)
  END AS mom_pct_change,
  -- alternative: safe percent change using prev_revenue=1 floor (useful when you prefer a numeric value)
  round(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 2) AS mom_pct_change_nullif,
  is_partial_month
FROM mom
WHERE month_start >= date_trunc('month', current_date) - interval '11 months'  -- last 12 months
ORDER BY month_start;